How to Check Valid Date Time from Sql Table

Question

I want to check either my current DateTime is between two DateTimes or not.

I have first time 2016-05-19 04:23:00.000 and second time 2016-05-19 04:50:00.000.

How to write a query to return true if current DateTime is between first and second time else return false?

There are only a few hundred or so examples if you google it. Here is an SO post with a solution...http://stackoverflow.com/questions/11745650/isdate-function-in-sql-evaluates-invalid-dates-as-valid — dinotom, May 20 '16 at 17:58

score 1 · Answer 1 · answered May 20 '16 at 18:26

1

A basic case expression can do this quite easily.

case when FirstTime <= getdate() AND getdate() <= SecondDate 
    then 'True' 
    else 'False' 
end

answered May 20 '16 at 18:26

Sean Lange

33,028
3
25
40

shadow · Accepted Answer · 2016-05-21T05:14:59.887

0

Select *
From Table
Where
  ( '2016-05-19 04:23:00.000' <= dateColumn )
  And ( dateColumn < '2016-05-19 04:50:00.000' )

edited May 21 '16 at 05:14

answered May 20 '16 at 18:02

shadow

1,883
1
16
24

what if I have time ''2016-05-19 04:23:00.000'' stored in table ? how I will get this time here ? – Jani Mani May 20 '16 at 18:11
@JaniMani See my edit and my comments on your accepted answer. Avoid using `Between` with `DateTimes`. – shadow May 21 '16 at 05:16

score 0 · Answer 3 · answered May 21 '16 at 05:40

Stop using between with datetimes unless you are ABSOLUTELY SURE that you know what you are doing and you ABSOLUTELY UNDERSTAND the datetime concept.

create table #test(
    Id int not null identity(1,1) primary key clustered,
    ActionDate datetime not null
)

insert into #test values
( '2015-12-31 23:59:59.99' ),
( '2016-01-01' ),
( '2016-01-10' ),
( '2016-01-31 23:59:59.99' ),
( '2016-02-01' )

select * from #test
-- all the rows
1   2015-12-31 23:59:59.990
2   2016-01-01 00:00:00.000
3   2016-01-10 00:00:00.000
4   2016-01-31 23:59:59.990
5   2016-02-01 00:00:00.000


-- lets locate all of January

-- using between
select * from #test
where
    ( ActionDate between '2016-01-01' and '2016-01-31' )

2   2016-01-01 00:00:00.000
3   2016-01-10 00:00:00.000
-- missing row 4

select * from #test
where
    ( ActionDate between '2016-01-01' and '2016-02-01' )

2   2016-01-01 00:00:00.000
3   2016-01-10 00:00:00.000
4   2016-01-31 23:59:59.990
5   2016-02-01 00:00:00.000 -- this is not January

-- using < and >
select * from #test
where
    ( '2016-01-01' <= ActionDate )
    and ( ActionDate < '2016-02-01' )

2   2016-01-01 00:00:00.000
3   2016-01-10 00:00:00.000
4   2016-01-31 23:59:59.990


drop table #test

How to Check Valid Date Time from Sql Table

3 Answers3