Archive

Posts Tagged ‘Convert Date’

SQL Trivia – How to convert milliseconds to [hh:mm:ss.ms] format

August 1, 2017 1 comment

 
Today for some reporting purpose I need to convert milliseconds to hh:mm:ss.ms format, i.e.

Hours : Minutes : Seconds . Micro-Seconds
 

So, I tried to create query below, of course by taking help from internet, so that I can have a sample code handy for future reference:

DECLARE @MilliSeconds INT
SET @MilliSeconds = 25289706

SELECT CONCAT(
		RIGHT('0' + CAST(@MilliSeconds/(1000*60*60) AS VARCHAR(2)),2), ':',						-- Hrs
		RIGHT('0' + CAST((@MilliSeconds%(1000*60*60))/(1000*60) AS VARCHAR(2)),2), ':',			-- Mins
		RIGHT('0' + CAST(((@MilliSeconds%(1000*60*60))%(1000*60))/1000 AS VARCHAR(2)),2), '.',	-- Secs
		((@MilliSeconds%(1000*60*60))%(1000*60))%1000											-- Milli Secs
) AS [hh:mm:ss.ms]

-- 7 Hrs, 1 minute, 29 seconds and 706 milliseconds

Advertisement
Categories: SQL Trivia Tags: ,

How to convert ‘20140620’ date to ‘DD-MM-YYYY’ format – MSDN TSQL forum

June 12, 2014 Leave a comment

–> Question:

I have data for date fields like “20140620”(YYYYMMDD) while inserting into the table it should be in the format “DD-MM-YYYY”

Can any one please help me to achive this.
 

–> My Response:

SELECT FORMAT(CAST('20140620' as DATE), 'dd-MM-yyyy', 'en-US'), CAST('20140620' as DATE)

… this will work with SQL Server 2012 and above.
 

–> Other Responses:

-- by Olaf Helper
SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY

-- by Latheesh NK
SELECT CONVERT(VARCHAR(10), '20140620', 105)

 

Ref Link.

Categories: Datatypes, SQL Server Questions Tags: ,