Date Issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Issues

Author

Topic

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2005-12-08 : 14:45:24

Hi

I have a column that has been defined as a smalldatatime datatype

when i run a query

Select * from Table where mydatecol = getdate()

It shows no results please help & also explain why it does not show

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-08 : 14:50:58

??? Because getdate() is going to be different every time you use it. Run SELECT GETDATE() 1800 times until your finger hurts and see if it ever has the same value as the last time.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-08 : 14:51:24

GetDate() returns a date and time IE (2005-12-08 14:50:52.670).
The reason you aren't getting records is none match the current date and time exactly try

Select * from Table where mydatecol between GetDate()-1 and GetDate()

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2005-12-08 : 15:00:20

Thankyou.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-08 : 15:11:05

if you want to include datetimes throughout the entire (current) day right up till midnight, you'll need something like this:

where myDateCol >= dateadd(day, datediff(day,0,getdate()), 0)
and myDateCol < dateadd(day, datediff(day,0,getdate()), 1)

Derrick, I've executed "select getdate()" more than 1800 times and my finger still don't hurt yet. what should i do.

Be One with the Optimizer
TG

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-08 : 15:25:01

Execute harder. It's a little like certain personal issues. If you don't succeed....push....push....push!!!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nu_dba
Starting Member

8 Posts

Posted - 2005-12-08 : 15:45:20

This is a guess, but it may have to do with the fact that you defined your column to be of smalldatetime and getdate() returns the datetime type. I would try casting the result to smalldatetime and see if you get any results.

-- nu_dba

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-09 : 01:05:31

More on Dates
http://www.sql-server-performance.com/fk_datetime.asp

>>I've executed "select getdate()" more than 1800 times and my finger still don't hurt yet. what should i do.


While not your_Finger_Get_hurted
	HIT select getdate()
Loop

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources