Convert from DateTime to INT

Question

In my SSIS package, I have to converting values from DateTime to a corresponding INTEGER value. The following sample has been provided.

Any ideas as to how I can convert these?

DATETIME   INT
---------  ----
1/1/2009   39814
2/1/2009   39845
3/1/2009   39873
4/1/2009   39904
5/1/2009   39934
6/1/2009   39965
7/1/2009   39995
8/1/2009   40026
9/1/2009   40057
10/1/2009  40087
11/1/2009  40118
12/1/2009  40148
1/1/2010   40179
2/1/2010   40210
3/1/2010   40238
4/1/2010   40269
5/1/2010   40299
6/1/2010   40330

Bandeau

SELECT CAST(CAST('20090101' AS DATETIME) AS INT) + 2? – Odell

Answer 1

EDIT: Casting to a float/int no longer works in recent versions of SQL Server. Use the following instead:

select datediff(day, '1899-12-30T00:00:00', my_date_field)
from mytable

Note the string date should be in an unambiguous date format so that it isn't affected by your server's regional settings.

---

In older versions of SQL Server, you can convert from a DateTime to an Integer by casting to a float, then to an int:

select cast(cast(my_date_field as float) as int)
from mytable

(NB: You can't cast straight to an int, as MSSQL rounds the value up if you're past mid day!)

If there's an offset in your data, you can obviously add or subtract this from the result

You can convert in the other direction, by casting straight back:

select cast(my_integer_date as datetime)
from mytable

Commutate

The OP doesn't have any time component (shown at least). datetime is stored as 2 ints so you could also cast as binary, and cast the first 4 bytes to int. SELECT CAST(substring(CAST(CAST('20090101 15:00:00' AS DATETIME) AS BINARY(8)),1,4) AS INT) – Odell

True - but better safe than sorry! – Commutate

It does not work ?! for date format it throws error "Explicit conversion from data type date to float is not allowed." and for datetime it throws "Conversion failed when converting date and/or time from character string." – Sigurd

Which version of SQL Server are you using @Muflix? – Commutate

Answer 2

select DATEDIFF(dd, '12/30/1899', mydatefield)

Hackler

Why do we use 12/30/1899 and not 12/31/1899? – Venosity

@SeanBrookins It's been a long while, but iirc it has to do with a date bug in Lotus Notes from way back in the early 80's, where Excel copied the behavior in order to be compatible with Notes, and everything else has since copied Excel. – Hackler

The fact that you responded and not somebody different is already astounding, but within 35 minutes no need to apologize at all! I checked and this does create compatibility with Excel, so that makes sense. Weird! – Venosity

I get notices for new comments :) – Hackler

Answer 3

Or, once it's already in SSIS, you could create a derived column (as part of some data flow task) with:

(DT_I8)FLOOR((DT_R8)systemDateTime)

But you'd have to test to doublecheck.

Outspeak

Answer 4

If you want to convert to an int and if you know the style of the date, like here 104 for 31.12.2024, then you can convert to the default date format 2024-12-31 with convert(date, my_date, 104), make this a char(10), and then replace '-' with '' so and make that an int.

select convert(int, replace(convert(char(10),convert(date, '31.12.2024', 104)), '-',''))

But this can be made shorter. The conversion to char itself can get a date style as well, see format date to 105 in SQL Server after casting:

select convert(char(10),convert(date, '31.12.2024', 104), 112)

Output:

'20241231'

And this only needs to be converted to int:

select convert(int, convert(char(10),convert(date, '31.12.2024', 104), 112))

Output is the integer 20241231.

Ghislainegholston