SQL Server create date

Question

Can we create a date using SQL Server 2005 ?

Example if I have year = 2010, month = 11, and day = 2, how can I convert or create it to datetime?

Below is if we write it in javascript

var date = new Date(2010, 10, 2, 0, 0, 0, 0);

and this is with oracle

SELECT TO_DATE('2010-11-02', 'YYYY-MM-DD') FROM DUAL

Thank you

Answer 1

If you're working with strings, and wanting to convert to a datetime data type, it's best to work with strings that can be unambiguously converted to datetimes. The following formats can all be converted to datetime values by SQL (either via CONVERT, or letting SQL Server do the conversion implicitly) without any ambiguity:

'20101102'

'2010-11-02T10:59:59'

'2010-11-02T10:59:59.997'

In all of the above formats, the date components are arranged as 4 digit year, then month, then day. Other formats that look trivially similar (e.g. '2010-11-02') may be converted to different values, depending on the language settings for your connection (but Dogget's answer is still valid, because he's using it in an ODBC literal expression, which is well defined - surrounding it with the {d } characters)