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
  • 216,147
  • 25
  • 84
  • 135
TJA
  • 167
  • 1
  • 2
  • 5
  • 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://learn.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
  • 216,147
  • 25
  • 84
  • 135