Convert from DateTime to INT

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?

  • Delimited Semi-Colon Text in SQL with Accumulated Count
  • How to delete all duplicate record but except one in SQL Server
  • SQL Server 2012 Columns to Single Row Seperated by Comma
  • How long does Access keep unflushed transactions locally?
  • Open SQL Server .mdf and .ldf files from recovery
  • Get Excel sheet into temp table using a script
    ---------  ----
    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

  • TSQL to get DISTINCT records ordered and then apply row numbers
  • ON is part of syntax
  • Repeating “in” statement in T-SQL - can it be put centrally somewhere?
  • How to list all dates between two dates
  • Loss of Unicode data - due to collation change?
  • SQL Server 2005 error when grouping using subquery
  • 3 Solutions collect form web for “Convert from DateTime to INT”

    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

    select DATEDIFF(dd, ’12/30/1899′, mydatefield)

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


    But you’d have to test to doublecheck.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.