Problem with Date Function ?

I insert date into table using getdate() function and that works fine.

Now from my query when I try to compare with it and returns nothing.

Thats how the date is stored in the table : 6/4/2007 1:46:57 PM

Now If I do

SELECT * FROM Sometable

WHERE date = getdate() ///Nothng returned

SELECT * FROM sometable

WHERE date = '6-4-2007' // Nothing is returned

How to Fix this issue ?

This has to do with the way that datetime data is stored. Try this:

Code Snippet

select * from someTable

where date >= convert(datetime(convert(varchar(10), getdate(), 101)))

and date < convert(datetime(convert(varchar(10), getdate()+1, 101)))

or

Code Snippet

select * from someTable

where date >= '6-4-2007'

and date < convert(datetime, '6-4-2007')+1

The problem with using the BETWEEN operator is that it includes events that occur exactly on midnight which is technically part of the next day. There are a number of other ways of doing this in addition to these two.

Something that you ought to avoid is loading either GETDATE() or your date constant '6-4-2007' into a variable. Loading this data into a variable eliminates some optimization options that might be exploited by the SQL optimizer.

|||

Hello Kent,

That will return me all dates greater than that specific date. I want the exact date, is there any way to change how the date is stored ? As i'm creating my SQL programatically, it is hard to have everything done at that level.

|||

Sorry my bad, I made a mistake in dates. That works !!!

Is there any way to fix this at Database Level....

|||

Harsimrat,

Datatime data type can include also time part, so if you need to select rows you will have to specify the time part you want.

select *

from sometable

where date >= '20070604' and date < '20070605'

-- or

select *

from sometable

where date between '2007-06-04T10:00:00' and '2007-06-04T18:59:59.997'

If you used function getdate() to insert the rows, then remember that this function returns current date and time and it will not be useful to match rows inserted in the past unless both operations are executed in the same time.

-- this could work

declare @.t table (c1 datetime)

insert into @.t values(getdate())

select * from @.t where c1 = getdate()

-- but not this

declare @.t table (c1 datetime)

insert into @.t values(getdate())

waitfor delay '00:00:00.003'

select * from @.t where c1 = getdate()

AMB

|||

Adding to Kent's explanition, when you store a datetime value as you have, then you will not get a match unless the criteria has the EXACT same datetime value -up to the milliseconds.

So to find all values for a particular date, you need to look for values since midnight, and up to the next midnight. Kent provided one set of criteria that accomplishes that goal, here is another.

--For today

Code Snippet

WHERE ( [Date] >= dateadd( day, datediff( day, 0, getdate() ), 0 )
AND [Date] < dateadd( day, datediff( day, 0, getdate() + 1 ), 0)

)

p.s., You really shouldn't name your columns and tables with 'RESERVED WORDS'. [Date] is a reserved word that has special meaning in SQL Server. Refer to Books Online, Topic: 'Reserved Words'

|||

When I run this query:

Code Snippet

declare @.someTable table
( date datetime
)
insert into @.someTable
select '6/3/7 15:00' union all
select '6/3/7 23:59:59.997' union all
select '6/4/7' union all
select '6/4/7 8:00' union all
select '6/4/7 23:59:59.997' union all
select '6/5/7'
--select * from @.someTable

select * from @.someTable
where date >= convert(datetime, (convert(varchar(10), getdate(), 101)))
and date < convert(datetime, (convert(varchar(10), getdate()+1, 101)))

I get this result:

Code Snippet

date
--
2007-06-04 00:00:00.000
2007-06-04 08:00:00.000
2007-06-04 23:59:59.997

Is that result incorrect?