locked
SQL 2012 - Convert DateTime to Interget value RRS feed

  • Question

  • Hello Experts, 

    Please help me converting DateTime value to Integer/Bigint Type value/key

    Example 
    --INPUT
    SELECT '2017-06-05 03:25:50.010' --GETDATE()

    /*Below is the output required from above Input*/
    SELECT 20170605032550  -- TAKE DATA VALUE  FROM YEAR TO SECONDS AND CREATE INTEGER VALUE  

    -- I want to avoid using Replace (to Replace ":" and "-") from existing input string,is there any other clean/better way of converting ?

    • Edited by Rihan8585 Monday, June 5, 2017 9:38 AM edit
    Monday, June 5, 2017 9:33 AM

Answers

  • try below

    select FORMAT(getdate(), 'yyyyMMddHHmmss'),getdate()
    
    declare @d datetime = '2017-06-05 03:25:50.010'
    
    select FORMAT(@d, 'yyyyMMddHHmmss'),@d


    Thanks Saravana Kumar C

    • Proposed as answer by Hilary CotterMVP Monday, June 5, 2017 1:29 PM
    • Marked as answer by Rihan8585 Tuesday, June 6, 2017 6:44 AM
    Monday, June 5, 2017 9:59 AM
  • Hi Rihan8585,

    Or you can try following method without Format() function:

    select GETDATE()
    
    select convert(varchar,getdate(),112)+
    substring(convert(varchar,getdate(),108),1,2)+
    substring(convert(varchar,getdate(),108),4,2)+
    substring(convert(varchar,getdate(),108),7,2)
    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rihan8585 Tuesday, June 6, 2017 6:44 AM
    Tuesday, June 6, 2017 1:37 AM

All replies

  • try below

    select FORMAT(getdate(), 'yyyyMMddHHmmss'),getdate()
    
    declare @d datetime = '2017-06-05 03:25:50.010'
    
    select FORMAT(@d, 'yyyyMMddHHmmss'),@d


    Thanks Saravana Kumar C

    • Proposed as answer by Hilary CotterMVP Monday, June 5, 2017 1:29 PM
    • Marked as answer by Rihan8585 Tuesday, June 6, 2017 6:44 AM
    Monday, June 5, 2017 9:59 AM
  • SELECT format(getdate(),'yyyyMMddHHmmss')
    Monday, June 5, 2017 10:37 AM
  • To add to the suggestions to use FORMAT, you can CAST the result as bigint to turn the value as an integer:

    SELECT CAST(FORMAT(SYSDATETIME(),'yyyyMMddHHmmss') AS bigint) AS yyyyMMddHHmmss;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 5, 2017 11:16 AM
  • I am wondering that what is your logic to do this with datetime value? Thanks.
    Monday, June 5, 2017 1:03 PM
  • Note the format functions work in SQL 2012 and above.
    Monday, June 5, 2017 1:29 PM
  • Hi Rihan8585,

    Or you can try following method without Format() function:

    select GETDATE()
    
    select convert(varchar,getdate(),112)+
    substring(convert(varchar,getdate(),108),1,2)+
    substring(convert(varchar,getdate(),108),4,2)+
    substring(convert(varchar,getdate(),108),7,2)
    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rihan8585 Tuesday, June 6, 2017 6:44 AM
    Tuesday, June 6, 2017 1:37 AM
  • Another way:

     declare @dtBigInt bigint
    --declare @dtstringFromDatetime varchar(14)

    select @dtBigInt=CAST(concat(year(getdate())
    ,Right('0'+Cast(month(getdate()) as varchar(2)),2)
    ,Right('0'+Cast(day(getdate()) as varchar(2)),2)
    ,Right('0'+Cast(datepart(hour, getdate()) as varchar(2)),2)
    ,Right('0'+Cast(datepart(minute,getdate()) as varchar(2)),2)
    ,Right('0'+Cast(datepart(second,getdate()) as varchar(2)),2)
    ) as BIGINT)
    print @dtBigInt


    Tuesday, June 6, 2017 3:50 PM
  • This makes no sense. Do you also think you can turn temporal data into colors? See how silly that is? SQL and the relational model are based on strong typing. What you seem to be trying to do is use the string representation of an ISO 8601 display format. Why? All you've done is destroy information. SQL is quite capable of handling temporal data

    --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, June 6, 2017 7:24 PM