CODEWITHSUNDEEP
sql-server
tsurahman
tsurahman

Reputation: 1912

SQL Server create date

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

Upvotes: 5

Views: 12840

Answers (6)

Doggett
Doggett

Reputation: 3464

You can do SELECT {d '2010-11-02'} have to use this exact format though

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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)

Upvotes: 3

Jørn Jensen
Jørn Jensen

Reputation: 998

Specify the style in the argument to the convert function. See the msdn help on convert for the different values.

select convert(datetime, '2010-11-02', 120)
...
2010-11-02 00:00:00.000

Upvotes: 1

Manfred Sorg
Manfred Sorg

Reputation: 1890

You can simply use set @datevar='20101102';. This is language-neutral and will be converted implicitly.

Upvotes: 0

pavanred
pavanred

Reputation: 13793

You can use select convert(datetime, '11-11-2010') to convert to datetime.

Upvotes: 0

codingbadger
codingbadger

Reputation: 43974

select convert(datetime, convert(char(8), 20101102))

Upvotes: 0

Related Questions