none
convert date time column to integer

    Question

  • Hi

    I have columns in the format as 2001-12-03 00:00:00:00:00 date time data type i want to convert them in to integer such as

    20011203 etc.. is that possible to convert what would be the SSIS expression or SQL Code for this

    help would be appricated

    Thank you


    Sri.Tummala

    Monday, March 12, 2012 7:31 PM

Answers

  • Hello,

    Convert first the date to ISO format yyyymmdd, then you can convert it directly to integer:

    DECLARE @date as datetime;
    SET @date = '2001-12-03 00:00:00.000'
    
    
    SELECT CONVERT(int, CONVERT(char(8), @date, 112)) AS DateInt 


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Christa Kurschat Monday, March 12, 2012 7:38 PM
    • Marked as answer by kali786 Monday, March 12, 2012 8:53 PM
    Monday, March 12, 2012 7:36 PM
  • One way

    DECLARE @t DATETIME = '2001-12-03 00:00:00:00'
    SELECT CAST(REPLACE(CONVERT(CHAR(10),@t,111),'/','') AS INT)


    Abdallah El-Chal, PMP, CIPP/IT, ITIL

    • Marked as answer by kali786 Monday, March 12, 2012 8:53 PM
    Monday, March 12, 2012 7:41 PM

All replies

  • Hello,

    Convert first the date to ISO format yyyymmdd, then you can convert it directly to integer:

    DECLARE @date as datetime;
    SET @date = '2001-12-03 00:00:00.000'
    
    
    SELECT CONVERT(int, CONVERT(char(8), @date, 112)) AS DateInt 


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Christa Kurschat Monday, March 12, 2012 7:38 PM
    • Marked as answer by kali786 Monday, March 12, 2012 8:53 PM
    Monday, March 12, 2012 7:36 PM
  • One way

    DECLARE @t DATETIME = '2001-12-03 00:00:00:00'
    SELECT CAST(REPLACE(CONVERT(CHAR(10),@t,111),'/','') AS INT)


    Abdallah El-Chal, PMP, CIPP/IT, ITIL

    • Marked as answer by kali786 Monday, March 12, 2012 8:53 PM
    Monday, March 12, 2012 7:41 PM
  • WHY?? This is insane !  Would you like to cast a date as a color? What animal should '2011-12-31' be?  

    Go back, read the foundations of data theory and the concept of domains. This time pay attention to the part about how they are orthogonal and why we never confuse symbols with actual values. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, March 13, 2012 3:40 AM
  • WHY?? This is insane !

    No, it's not. In a data warehouse this is quite common to use an integer for date key in this way. It's more performant and saves space. Have a look at the AdventureWorks DW 2008/2008R2 sample database, there the date key is an integer value as well.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, March 13, 2012 5:40 AM