question

Gnick77 avatar image
Gnick77 asked

Convert date string to sql number

Hi, i have a column with dates and type string. How can i convert this column to numbers? For example i have the date 02/01/2004 (dd/mm/yyyy) as type string and i want to convert it to number? Until now i have found the following query "SELECT cast(cast('02/01/2004' as datetime) as bigint)" but it recognises the date as "mm/dd/yyyy" and returns "38016" instead of "37986". So i suppose i have first to convert it to mm/dd/yyyy but i don't know how to do that. Thank you very much George
sql-server-2008datecast-convert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gnick77 avatar image
Gnick77 answered
Maybe this is the solution: select cast(cast(convert(varchar, cast('02/01/2004' as datetime), 103) as datetime) as bigint).
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gnick77 avatar image Gnick77 commented ·
In the above query an error occured when date= 13/01/2004 (dd/mm/yyyy) maybe because sql recognises "13" as a month. So i don't know what to do
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
It's about whether 02/01/2004 is 2nd January 2004 (37986) or 1st February 2004 (38016). The server interprets it differently depending on the language of the login that's connected. US English (really US English) sees it as 1st February but British English sees it as 2nd January. If you pass it in ISO format (yyyymmdd) it should work correctly whatever the language.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gnick77 avatar image
Gnick77 answered
I have found the solution select cast(convert(DATETIME, column_name, 103) as bigint) from database_name
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
If your column is in this 99/99/9999 format this code is still relying on how the server interprets which pair of digits is the month and which is the day. String it together in ISO format first. One of these will work depending on whether your column is dd/mm/yyyy or mm/dd/yyyy: declare @a varchar(10) = '02/01/2004'; select cast(substring(@a, 7, 4) + substring(@a, 1, 2) + substring(@a, 4, 2) as date); select cast(substring(@a, 7, 4) + substring(@a, 4, 2) + substring(@a, 1, 2) as date);
1 Like 1 ·
Gnick77 avatar image Gnick77 commented ·
Thank you very much
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.