'Converting date and time to Millisecond Format in SQL

I have a column containing the date and time, and I want to convert these column to be "yyyy-mm-dd -hh.mm.ss.nnnn"

My data as follow:

POST_DATE TRX_TIME
2022-03-03 112029
2022-03-03 114143

The format that I want is as follows "2022-02-23 -09.53.00.000"

I've searched and tried to modify my data using the following query

Select post_date, trx_time
    , convert(varchar, cast(post_date as varchar)+'-'+trx_time,114) 'hh:mi:ss:mmm'
from stg_trx_cz

and the result is I can't get the millisecond.

Is it possible with the data I have I can make the same format with the example?

sqlsql-server


Solution 1:[1]

Generally when working with datetimes, it is highly recommended to use native data types as it makes everything else easier i.e. formatting.

So first I'd convert it to a datetime2(3) and then just call the FORMAT() function

Formatting Datetime with Milliseconds

DECLARE @Post_Date DATE = '2022-03-03'
    ,@TRX_TIME VARCHAR(10) = '112029'

/*First convert to actual datetime*/
DECLARE @ActualDateTime DATETIME2(3) = (SELECT CAST(CONCAT(@Post_Date,' ',STUFF(STUFF(@TRX_TIME,3,0,':'),6,0,':')) AS DATETIME2(3)))

/*Then format*/
SELECT FORMAT(@ActualDateTime,'yyyy-MM-dd -hh.mm.ss.fff')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephan

Related Questions