|
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
(comments are locked)
|
|
Maybe this is the solution: select cast(cast(convert(varchar, cast('02/01/2004' as datetime), 103) as datetime) as bigint). 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
Oct 01 '11 at 12:12 PM
Gnick77
(comments are locked)
|
|
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.
(comments are locked)
|
|
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);
Oct 01 '11 at 12:34 PM
David Wimbush
Thank you very much
Oct 01 '11 at 12:56 PM
Gnick77
(comments are locked)
|
