CODEWITHSUNDEEP
sqlsql-servert-sql
Student
Student

Reputation: 21

Date Format Convert

I am dealing with sql DB where the date is recorded in this format '2020-348T08:55'. I need to convert this to datetime.

Please guide if there is possible way to do that?

Upvotes: 1

Views: 76

Answers (1)

forpas
forpas

Reputation: 164064

With string and date/time functions extract the year and days to create a DATETIME without the time and finally add the time part:

SELECT 
  CAST(DATEADD(
    DAY, 
    CAST(SUBSTRING(LEFT(col, CHARINDEX('T', col) - 1), 6, LEN(col)) AS INTEGER), 
    DATEFROMPARTS(CAST(LEFT(col, 4) AS INTEGER) - 1, 12, 31)
  ) AS DATETIME) + 
  CAST(RIGHT(col, 5) AS DATETIME) AS result
FROM tablename

If the days part is always a 3 digit number, it can be simplified:

SELECT 
  CAST(DATEADD(
    DAY, 
    CAST(SUBSTRING(col, 6, 3) AS INTEGER), 
    DATEFROMPARTS(CAST(LEFT(col, 4) AS INTEGER) - 1, 12, 31)
  ) AS DATETIME) + 
  CAST(RIGHT(col, 5) AS DATETIME) AS result
FROM tablename

Replace col with your column's name.
See the demo.
Result:

> | result                  |
> | :---------------------- |
> | 2020-12-13 08:55:00.000 |

Upvotes: 1

Related Questions