CODEWITHSUNDEEP
sqlsql-servert-sqldatetimestored-procedures
Liam neesan
Liam neesan

Reputation: 2561

How to compare datetime in SQL Server?

I have to show the date 2018/01/30 if the datetime is between 2018/01/30 04:59:59.000 and 2018/01/31 04:59:59.000

I have a table called DataEntry. I want to move those records by date as per my criteria.

This DataEntry table have TransferTime that datatype is datetime.

As per my criteria if the TransferTime is 2018/01/30 01:30:59.000 then the date should be 2018/01/29

Upvotes: 1

Views: 619

Answers (3)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

I think you can simply just write like this:

select 
 case when DATEPART(HOUR,'2018/01/30 01:30:59.000') >= 05 then cast('2018/01/30 01:30:59.000' as date) 
      else cast(dateadd(Dd,-1,'2018/01/30 01:30:59.000' )as date) 
 end

Upvotes: 2

Jayvee
Jayvee

Reputation: 10875

this will do too:

select cast(dateadd(second, -17999,Transfertime) as date)

being 17999 = 4hs59m59s in seconds

Upvotes: 1

Thom A
Thom A

Reputation: 95689

This is somewhat of a guess on vague logic, but perhaps using CONVERT and DATEADD?

WITH VTE AS(
    SELECT CONVERT(datetime2(3),DT) AS DT
    FROM (VALUES('20180130 04:59:59.000'),('20180131 01:00:34.000'),('20180130 01:30:59.000')) V(DT))
SELECT CONVERT(date, DATEADD(SECOND, -17999,DT)) AS D, DT
FROM VTE;

It's worth noting that you, oddly, state that '20180130 04:59:59.000' AND ''20180131 04:59:59.000' should both be on the same day, ('20180130'). This doesn't make any sense, as Days don't overlap like that. Thus the latter time would show '20180131', as it's exactly 24 hours later.

If the former time should actually be '20180129', then change -17999 to -18000, or SECOND,-17999 to HOUR, -5.

Upvotes: 1

Related Questions