What's the canonical way to create a date from three numbers representing days, months and years?

Question

I have a year 2009, a month 11 and a day 12. All of these are being passed to me as integers. What I want to do is create a date from these. I want to use these as part of my where statement.

What is the best way to do it from either a clean code and most importantly a speed point of view?

I personally am using MS SQL, which I'm sure will make a difference.

Thanks in advance,

Alex.

Answer 1

Are you talking about combining them to store in a database or you have them stored that way and want to combine them in a query and convert to a dte to use date math functions?

Answer 2

The most unambiguous formats, using only integers, are YYYYMMDD, YYYY-MM-DD, and YYYY/MM/DD. I can't speak to MSSQL specifically, but in my experience, YYYY-MM-DD is the most universally supported (due in no small part to it being part of the ISO 8601 standard).

Answer 3

Assuming that your INTs are called @d, @m and @y:

DATEADD(month, ((@y - 1900) * 12) + @m - 1, @d - 1)

EDIT

Please note that this technique isn't "canonical" in any way, it relies on manipulating SQL Server's implementation of DATETIME.

If you pass invalid values for @d, @m or @y then you'll get an invalid DATETIME in return:

• { @y=2009, @m=11, @d=12 } returns 2009-11-12 (correct)
• { @y=2009, @m=11, @d=50 } returns 2009-12-19 (wrong)
• { @y=2009, @m=13, @d=12 } returns 2010-01-12 (wrong)

Answer 4

As mentioned by Ben yyyy-mm-dd (the ANSI standard) is a pretty safe bet, as its not as ambiguous as dd mm yyyy or mm dd yyyy. SQL Server will happily convert a varchar passed in that format to a date.