Decimal to Date in SQL

Question

Hi,

I have a column MGDT having decimal data type in one table. I need to convert it to date and populate the data into NMDT column in other table whose data type is datetime2 in SQL SERVER..

The value in the column MGDT - 202310

Want to get output like : 2023-10-01 00:00:00 0000000

Could anyone please help me in this regard?

Answer 1

One method to convert the decimal YYYYMM value to a datetime2 is to append '01' to form an ISO 8601 date format string (YYYYMMDD). The example below also includes TRY_CAST to will set invalid dates to NULL.

SET MNDT = TRY_CAST(CAST(MGDT AS char(6)) + '01' AS datetime2)