CODEWITHSUNDEEP
sql-serverdatetimegetdatesql-server-2008r2-expresssysdatetime
Jani Mani
Jani Mani

Reputation: 27

How to Check Valid Date Time from Sql Table

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?

Upvotes: 0

Views: 1470

Answers (3)

shadow
shadow

Reputation: 1903

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

Upvotes: 0

shadow
shadow

Reputation: 1903

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

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

A basic case expression can do this quite easily.

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

Upvotes: 1

Related Questions