Date literals in expressions?

How do I specify a date literal in an expresison? It's not covered in Books Online. None of the following worked:

mydate == '1899-12-30'

mydate == "1899-12-30"

mydate == #1899-12-30#

This did work:

mydate == (DT_DATE) 0

but it's not self-explanatory and it would be utterly stupid if that's the only way to specify a date literal. Are we once again victims of the "rushed-out-the-door" syndrome?

Jamie already experimented on the subject.

This should help you.

http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx

Regards,

Yitzhak

|||"12/30/1899"?|||So then I was right, huh? Outside of explicit casting there's no way to deal with date literals? <Sigh> Will SSIS be "fixed" in Katmai? I certainly hope so, although it would be nice if we'd get a service pack for 2005 as well....|||

Phil Brammer wrote:

"12/30/1899"?

It has nothing to do with the date format. When using apostrophes, SSIS complains that the apostrophe is an unexpected character. When using quotation marks, it complains that DT_DATE can't be implicitly converted to DT_WSTR. And number (pound) signs are the syntax for direct references to lineage IDs.

|||I don't know what the issue is.

What are you trying to do?|||

Okay, here's the full story. I'm importing from a dBASE III file. It has a date column. Sometimes the date is NULL, but when you bring "null" dates straight to SQL Server via SSIS (specifically via the Jet OLEDB driver) they do not become NULL but rather are treated as date 0, which equates to 1899-12-30 12:00:00 AM in Jet. I'm trying to test for this value in a Derived Column transformation, hence the need for a date literal. Basically, I wanted to do this:

MyDate | Replace 'MyDate' | MyDate == '1899-12-30' ? NULL(DT_DATE) : MyDate | database date [DT_DBDATE]

But I couldn't figure out a non-casting way to specify a date literal in the expression, hence the question.

This expression worked for me:

MyDate == (DT_DATE) 0 ? NULL(DT_DATE) : MyDate

but I wasn't happy with it.

|||Since SSIS is strongly-typed, and based on a C# syntax, I'm not sure why this comes as a surprise. Personally I prefer it this way. But that's just my opinion |||

The SSIS expression language only has support for numeric, string, and Boolean literals, as documeneted in Books Online - http://msdn2.microsoft.com/en-us/library/a980cd52-54ef-4b9c-b00c-e6807cf8e01f(SQL.90).aspx

sql

Date in current Month?

I want to know if @.MyDate (DateTime) is in the current month. This works,
but it's ugly. Any ideas to make it look nicer?
if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
datepart(month,current_timestamp) = datepart(month,@.MyDate))
/* Yes*/
else
/* No */Is this any prettier?
If CONVERT(char(6),@.MyDate,112) = CONVERT(char(6),CURRENT_TIMESTAMP,112)
/* yes */
Else
/* no */
Gert-Jan
JM wrote:
> I want to know if @.MyDate (DateTime) is in the current month. This works,
> but it's ugly. Any ideas to make it look nicer?
> if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
> else
> /* No */|||Yes, your's is much better since it doesn't have two parts. It's always
helpful whenever I try something, then get a push from an expert. I learn a
lot that way.
Thanks!
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:421A38B5.98921D2E@.toomuchspamalready.nl...
> Is this any prettier?
> If CONVERT(char(6),@.MyDate,112) = CONVERT(char(6),CURRENT_TIMESTAMP,112)
> /* yes */
> Else
> /* no */
> Gert-Jan
>
> JM wrote:
works,|||Try this:
If @.MyDate >= dateadd(month,datediff(month, '20000101', getdate()), '
20000101' )
and @.MyDate < dateadd(month,1+datediff(month, '20000101', getdate()), '
20000101' )
...
Steve Kass
Drew University
JM wrote:

>I want to know if @.MyDate (DateTime) is in the current month. This works,
>but it's ugly. Any ideas to make it look nicer?
>if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
>else
> /* No */
>
>
>|||Try,
if datediff(month, @.MyDate, GETDATE()) = 0
print 'yes'
else
print 'no'
AMB
"JM" wrote:

> I want to know if @.MyDate (DateTime) is in the current month. This works,
> but it's ugly. Any ideas to make it look nicer?
> if (datepart(year,current_timestamp) = datepart(year,@.MyDate) and
> datepart(month,current_timestamp) = datepart(month,@.MyDate))
> /* Yes*/
> else
> /* No */
>
>
>

date between

hi, anybody know an easy way to select fields between two dates? I have tried dateadd like DATEADD(day, 28, myDate) but the result I want is from i.e 07.15 to 08.15.

I have also tried something like CONVERT(varchar, myDate, 104) BETWEEN '07.15.2002' AND '08.15.2002'

anyone have another suggestion?Originally posted by catorene RE: hi, anybody know an easy way to select fields between two dates? I have tried dateadd like DATEADD(day, 28, myDate) but the result I want is from i.e 07.15 to 08.15. I have also tried something like CONVERT(varchar, myDate, 104) BETWEEN '07.15.2002' AND '08.15.2002'
anyone have another suggestion?

Q1 [Anybody know an (easy?) way to select fields between two dates?]
A1 It isn't really clear what you are asking about. A between syntax example?:

SELECT ord_date AS DatesBetween19930221And19940913
FROM pubs.dbo.sales
WHERE (ord_date Between CONVERT(DATETIME, '1993-02-21 00:00:00', 102) AND CONVERT(DATETIME, '1994-09-13 00:00:00', 102))
ORDER BY ord_date

SELECT ord_date AS DatesBetween19930221And19940913
FROM pubs.dbo.sales
WHERE (ord_date > CONVERT(DATETIME, '1993-02-21 00:00:00', 102)) AND (ord_date < CONVERT(DATETIME, '1994-09-13 00:00:00', 102))
ORDER BY ord_date|||You actually were on the right track - it is just that style 104 is the format dd.mm.yy not mm.dd.yy like in your between clause. So if you modified your dates in the between clause to be 15.07.2002 and 15.08.2002 you would be ok. Also, you could use style 101 but you would need slashes instead of periods - and your date 07/15/2002 and 08/15/2002 would work as well.