0

How to convert this piece of code from Oracle to get the same behaviour in SQL Server ?

select to_date('20141008174025', 'YYYYMMDDHH24MISS') 
from dual
GMB
  • 195,563
  • 23
  • 62
  • 110
TJA
  • 117
  • 1
  • 1
  • 4
  • 1
    Already asked and answered: https://stackoverflow.com/questions/45074493/use-to-date-in-sql-server-2012 – pifor Jun 05 '20 at 13:37
  • Unfortunately, there isn't an exact equivalent. `CONVERT()` only supports a limited catalogue of fixed [formats](https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles). – Álvaro González Jun 05 '20 at 13:44

1 Answers1

1

One method uses datetimefromparts():

declare @str varchar(20) = '20141008174025';
select datetimefromparts(
    substring(@str, 1, 4), 
    substring(@str, 5, 2), 
    substring(@str, 7, 2), 
    substring(@str, 9, 2),
    substring(@str, 11, 2),
    substring(@str, 13, 2),
    0
)

You can also do:

declare @str varchar(20) = '20141008174025';
select cast(
    left(@str, 8) 
        + ' ' 
        + substring(@str, 9, 2) 
        + ':' 
        + substring(@str, 11, 2) 
        + ':' + substring(@str, 13, 2)
    as datetime    
)
GMB
  • 195,563
  • 23
  • 62
  • 110