INCORRECT DATETIME format for transactions between 12 am to 1 am

Category: sql server getstarted


vlad_abz on Tue, 14 Nov 2017 13:10:04

Hello Guys,

I need some help with datetime format conversion.

I have written following query for getting the desired datetime format.

select replace(convert(varchar(8), @d, 112)+convert(varchar(8), @d, 112), ':','') from TableName

The result is coming correctly as follows - 20171114221045 which is correct.

BUT !!!!!!!!!! If I fire the same query between 12:00:00 am to 12:59:59 am, I am getting the result as follows -

201711142104. It is actually ommitting the "HOUR" which should come as "00".

i.e. the result should be - 20171114002104.



Stefan Hoffmann on Tue, 14 Nov 2017 13:21:17

First of all: craft a concise and complete example. This includes table DDL as well as sample data INSERT statements as runnable T-SQL script. Especially in your case: create an invariant example.

Hint: You didn't tell us anything about @d.. What data type, what content?

Then your given snippet just concatenates two dates, not a time..


        d DATETIME

INSERT INTO @Sample ( d )
       ( '20171114 22:10:45' ) ,
       ( '20171114 00:22:44' );

SELECT REPLACE(CONVERT(VARCHAR(8), S.d, 112) + CONVERT(VARCHAR(8), S.d, 112), ':', '') AS yours ,
       FORMAT(S.d, 'yyyyMMddHHmmss')
FROM   @Sample S;

Tom Phillips on Tue, 14 Nov 2017 13:22:30

The format for the 2nd convert it incorrect.

DECLARE @d datetime;
SET @d = '2017-11-14 00:21:04'

select replace(convert(varchar(8), @d, 112)+convert(varchar(8), @d, 114), ':','') 

select replace(replace(replace(CONVERT(varchar(25), @d, 120),':',''),'-',''),' ','')

Visakh16 on Tue, 14 Nov 2017 13:27:13

wont this be enough?

select format(d,'yyyyMMddHHmmss')  from TableName

Tom Phillips on Tue, 14 Nov 2017 14:02:26

That code only works in SQL 2012+.  The OP did not say the version they are using.

vlad_abz on Wed, 15 Nov 2017 06:07:47

Hello Guys,

Extremely sorry for the lack of information from my side.

My client is using sql server 2008 where it is not possible to use the Format('d',yyyymmddhhmmss) Function.

My code is as follows:

declare @d date,@t time

set @d='25/05/2017'

set @t='9:45:04'

select {fn concat(convert(varchar(8),@d, 112),convert(varchar(8),@t,112))} from TableName

Where I am getting the output as 2017052594504.

However, I want the output as 20170525094504.

Please help!


Visakh16 on Wed, 15 Nov 2017 06:39:41

then your best bet is this

declare @d date,@t time

set @d='25/05/2017'

set @t='9:45:04'

select REPLACE(REPLACE(REPLACE(CONVERT(varchar(19),DATEADD(ss,DATEDIFF(ss,0,@t),CAST(@d as datetime)) ,121),'-',''),':',''),' ','')