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
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.
I have found the solution select cast(convert(DATETIME, column_name, 103) as bigint) from database_name
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);
