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