locked
convert string to date and time RRS feed

  • Question

  • i have below string. i would like to convert it to date and time

    Input: [20/July/2019:09:10:11 +0000]

    Expected output: date: 2019-07-20, time: 09:10:11 with timezone

    Friday, February 28, 2020 4:26 PM

Answers

  • DECLARE @DateTime VARCHAR(200)
    SET @DateTime='[20/July/2019:09:10:11 +0000]'
    
    SELECT CAST( CAST(REPLACE(REPLACE(REPLACE((STUFF(@datetime,CHARINDEX(':',@datetime,1),1,' ')),']',''),'[',''),'+0000','') AS DATETIME) as date) AS [Date],
    CAST( CAST(REPLACE(REPLACE(REPLACE((STUFF(@datetime,CHARINDEX(':',@datetime,1),1,' ')),']',''),'[',''),'+0000','') AS DATETIME2(0)) as time) AS [Time]
    


    Hope it Helps!!

    • Marked as answer by SQL Insane Friday, February 28, 2020 5:15 PM
    Friday, February 28, 2020 4:49 PM
  • declare @strdt varchar(35)='20/July/2019:09:10:11 +0000'
    ;with mycte as (
    select parsename(replace(@strdt,'/','.'),1) col1
    ,parsename(replace(@strdt,'/','.'),2) col2
    ,parsename(replace(@strdt,'/','.'),3) col3
    )
    
    ,mycte2 as (
    select col1,col2,col3,
    parsename(replace(col1,':','.'),1) c1
    ,parsename(replace(col1,':','.'),2) c2
    ,parsename(replace(col1,':','.'),3) c3
    ,parsename(replace(col1,':','.'),4) c4
    from mycte)
    
    select  
     try_cast (
    concat(col2,' ', col3,' ',c4,' ', c3,':',c2,':',stuff(c1,7,0,':') )
     as datetimeoffset(7))
    from mycte2
    

    • Marked as answer by SQL Insane Friday, February 28, 2020 5:15 PM
    Friday, February 28, 2020 4:56 PM

All replies

  • DECLARE @DateTime VARCHAR(200)
    SET @DateTime='[20/July/2019:09:10:11 +0000]'
    
    SELECT CAST( CAST(REPLACE(REPLACE(REPLACE((STUFF(@datetime,CHARINDEX(':',@datetime,1),1,' ')),']',''),'[',''),'+0000','') AS DATETIME) as date) AS [Date],
    CAST( CAST(REPLACE(REPLACE(REPLACE((STUFF(@datetime,CHARINDEX(':',@datetime,1),1,' ')),']',''),'[',''),'+0000','') AS DATETIME2(0)) as time) AS [Time]
    


    Hope it Helps!!

    • Marked as answer by SQL Insane Friday, February 28, 2020 5:15 PM
    Friday, February 28, 2020 4:49 PM
  • declare @strdt varchar(35)='20/July/2019:09:10:11 +0000'
    ;with mycte as (
    select parsename(replace(@strdt,'/','.'),1) col1
    ,parsename(replace(@strdt,'/','.'),2) col2
    ,parsename(replace(@strdt,'/','.'),3) col3
    )
    
    ,mycte2 as (
    select col1,col2,col3,
    parsename(replace(col1,':','.'),1) c1
    ,parsename(replace(col1,':','.'),2) c2
    ,parsename(replace(col1,':','.'),3) c3
    ,parsename(replace(col1,':','.'),4) c4
    from mycte)
    
    select  
     try_cast (
    concat(col2,' ', col3,' ',c4,' ', c3,':',c2,':',stuff(c1,7,0,':') )
     as datetimeoffset(7))
    from mycte2
    

    • Marked as answer by SQL Insane Friday, February 28, 2020 5:15 PM
    Friday, February 28, 2020 4:56 PM