none
Best way to convert a 17 digit string to Datetime (UK)

    Question

  • Hi what is the best way to convert below into a datetime datatype? I have confirmed these are valid date and times

    Example and expected/desired outcome:

    20180531141323327 = 31/05/2018 14:13:23.327

    20180531141323327
    20180607095036968
    20180614162423843
    20180619132731827
    20180619132757301
    20180713160844223
    20180718104338476
    20180718104400799
    20180719113551667
    20180813143754397

    Thursday, April 18, 2019 8:29 AM

Answers

  • Hi what is the best way to convert below into a datetime datatype?

    By doing it ...

    ;with demo AS
    (select '20180531141323327' as string
     union all select '20180607095036968')
    
    select string,
           CONVERT(datetime,
           SUBSTRING(string, 1, 4) + '-' 
           + SUBSTRING(string, 5, 2) + '-'
           + SUBSTRING(string, 7, 2) + ' ' 
           + SUBSTRING(string, 9, 2) + ':'
           + SUBSTRING(string, 11, 2) + ':'
           + SUBSTRING(string, 13, 2) + '.'
           + SUBSTRING(string, 15, 3)
           , 121) AS DateAndTime
    from demo


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 8:47 AM
  • DECLARE @dt VARCHAR(50)='20180531141323327'
    SELECT
      
      DATEFROMPARTS(SUBSTRING(@dt, 1, 4) ,SUBSTRING(@dt, 5, 2) , SUBSTRING(@dt, 7, 2) )
        AS DATE_FROMPARTS

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 9:13 AM
    Answerer
  • Here the SQL for only the date:

    ;with demo AS
    (select '20180531141323327' as string
     union all select '20180607095036968')
    
    select string,
           CONVERT(date,
           SUBSTRING(string, 1, 4) + '-' 
           + SUBSTRING(string, 5, 2) + '-'
           + SUBSTRING(string, 7, 2)
           , 121) AS DateAndTime
    from demo


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 9:13 AM
  • As alternative

    DECLARE @dt VARCHAR(50)='20180531141323327'
    DECLARE @Date INT = LEFT(@dt,8),
    @Time INT = SUBSTRING(@dt,9,6);


    SELECT DATETIMEFROMPARTS(@Date / 10000, (@Date / 100) % 100, 
    @Date % 100, @Time / 10000, (@Time / 100) % 100, @Time % 100, 0);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:22 AM
    Thursday, April 18, 2019 9:18 AM
    Answerer
  •  SELECT  FORMAT(CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(20180531141323327,15,0,'.'),13,0,':'),11,0,':'),9,0,' ')),'dd/MM/yyyy HH:mm:ss.fff') --format your result to UK datetime
    ,Format(Cast(stuff(stuff(stuff(stuff ('20180531141323327', 9,0,' '),12,0,':'),15,0,':'),18,0,'.')  as datetime),'dd/MM/yyyy HH:mm:ss.fff')

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 2:41 PM
    Thursday, April 18, 2019 2:33 PM
    Moderator
  • This was another suggestion too, thanks Olaf.

    SELECT CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(20180531141323327,15,0,'.'),13,0,':'),11,0,':'),9,0,' '))

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 9:00 AM

All replies

  • Hi what is the best way to convert below into a datetime datatype?

    By doing it ...

    ;with demo AS
    (select '20180531141323327' as string
     union all select '20180607095036968')
    
    select string,
           CONVERT(datetime,
           SUBSTRING(string, 1, 4) + '-' 
           + SUBSTRING(string, 5, 2) + '-'
           + SUBSTRING(string, 7, 2) + ' ' 
           + SUBSTRING(string, 9, 2) + ':'
           + SUBSTRING(string, 11, 2) + ':'
           + SUBSTRING(string, 13, 2) + '.'
           + SUBSTRING(string, 15, 3)
           , 121) AS DateAndTime
    from demo


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 8:47 AM
  • This was another suggestion too, thanks Olaf.

    SELECT CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(20180531141323327,15,0,'.'),13,0,':'),11,0,':'),9,0,' '))

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 9:00 AM
  • Hi what is the best way to convert below into a datetime datatype?

    By doing it ...

    ;with demo AS
    (select '20180531141323327' as string
     union all select '20180607095036968')
    
    select string,
           CONVERT(datetime,
           SUBSTRING(string, 1, 4) + '-' 
           + SUBSTRING(string, 5, 2) + '-'
           + SUBSTRING(string, 7, 2) + ' ' 
           + SUBSTRING(string, 9, 2) + ':'
           + SUBSTRING(string, 11, 2) + ':'
           + SUBSTRING(string, 13, 2) + '.'
           + SUBSTRING(string, 15, 3)
           , 121) AS DateAndTime
    from demo
    Huge thanks Olaf, and how return just the date??


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, April 18, 2019 9:05 AM
  • DECLARE @dt VARCHAR(50)='20180531141323327'
    SELECT
      
      DATEFROMPARTS(SUBSTRING(@dt, 1, 4) ,SUBSTRING(@dt, 5, 2) , SUBSTRING(@dt, 7, 2) )
        AS DATE_FROMPARTS

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 9:13 AM
    Answerer
  • Here the SQL for only the date:

    ;with demo AS
    (select '20180531141323327' as string
     union all select '20180607095036968')
    
    select string,
           CONVERT(date,
           SUBSTRING(string, 1, 4) + '-' 
           + SUBSTRING(string, 5, 2) + '-'
           + SUBSTRING(string, 7, 2)
           , 121) AS DateAndTime
    from demo


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:17 AM
    Thursday, April 18, 2019 9:13 AM
  • As alternative

    DECLARE @dt VARCHAR(50)='20180531141323327'
    DECLARE @Date INT = LEFT(@dt,8),
    @Time INT = SUBSTRING(@dt,9,6);


    SELECT DATETIMEFROMPARTS(@Date / 10000, (@Date / 100) % 100, 
    @Date % 100, @Time / 10000, (@Time / 100) % 100, @Time % 100, 0);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 9:22 AM
    Thursday, April 18, 2019 9:18 AM
    Answerer
  •  SELECT  FORMAT(CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(20180531141323327,15,0,'.'),13,0,':'),11,0,':'),9,0,' ')),'dd/MM/yyyy HH:mm:ss.fff') --format your result to UK datetime
    ,Format(Cast(stuff(stuff(stuff(stuff ('20180531141323327', 9,0,' '),12,0,':'),15,0,':'),18,0,'.')  as datetime),'dd/MM/yyyy HH:mm:ss.fff')

    • Marked as answer by SimonKEvans Thursday, April 18, 2019 2:41 PM
    Thursday, April 18, 2019 2:33 PM
    Moderator