x

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
more ▼

asked Oct 01, 2011 at 11:57 AM in Default

Gnick77 gravatar image

Gnick77
17 4 4 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest
Maybe this is the solution: select cast(cast(convert(varchar, cast('02/01/2004' as datetime), 103) as datetime) as bigint).
more ▼

answered Oct 01, 2011 at 12:04 PM

Gnick77 gravatar image

Gnick77
17 4 4 5

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, 2011 at 12:12 PM Gnick77
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 01, 2011 at 12:17 PM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

(comments are locked)
10|1200 characters needed characters left

I have found the solution

select cast(convert(DATETIME, column_name, 103) as bigint) from database_name
more ▼

answered Oct 01, 2011 at 12:18 PM

Gnick77 gravatar image

Gnick77
17 4 4 5

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, 2011 at 12:34 PM David Wimbush
Thank you very much
Oct 01, 2011 at 12:56 PM Gnick77
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x29
x27

asked: Oct 01, 2011 at 11:57 AM

Seen: 1511 times

Last Updated: Oct 01, 2011 at 11:57 AM