compare date with current date

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

SQL Server 2000 Forums

Transact-SQL (2000)

compare date with current date

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-28 : 18:42:29

I have a table with date field "StartDate".
I want to get all records where (StartDate)>= (Current Date with time 00:00:00)

Please help me.
Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-28 : 18:50:36

This will do it:

StartDate >= Convert(datetime, Convert(int, GetDate()))

The inside brackets convert the date to an integer (38195 for today) and the outside ones convert it back into a date.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-28 : 19:05:00

Thanks Timmy!
What about if i want to see current date in "DD-MM-YYYY" format without time.
Thanks
Garg

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-28 : 19:21:17

You can use CONVERT(varchar, GetDate(), xxx)

xxx is a number indicating the format of date you need. Check out the CONVERT function in Books Online for more info.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 09:27:38

quote:
StartDate >= Convert(datetime, Convert(int, GetDate()))

Careful here.

select Convert(datetime, Convert(int, convert(datetime,'7/29/2004 09:26:00')))

select Convert(datetime, Convert(int, convert(datetime,'7/29/2004 17:26:00')))

returns two different results in the "same day".

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 09:29:32

In a similar idea.

select Convert(datetime, FLOOR(Convert(real, convert(datetime,'7/29/2004 09:26:00'))))
select Convert(datetime, FLOOR(Convert(real, convert(datetime,'7/29/2004 17:26:00'))))

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-29 : 18:19:20

Thanks Friends

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 01:57:12

If you just want the date, and drop the time, then

DATEADD(day, DATEDIFF(day, 0, MyDateColumn), 0)

is the fastest I have found. Works for Month / Minute etc. too

(I did some timing comparisons in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35296, FLOOR/REAL takes almost twice as long, although that is better than convert to VARCHAR(8) which takes 6 times as long ...)

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-30 : 02:29:52

SELECT DATEADD(day, DATEDIFF(day, 0, getdate()), 0)

Very cool. I have never seen that. Thanks for the benchmarks Kristen. Another one for your blog. ;)

--Ken
Your Kung-Fu is not strong. -- 'The Core'

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-30 : 02:37:04

Thanks Kristen.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-30 : 07:30:54

quote:
Originally posted by Kristen

DATEADD(day, DATEDIFF(day, 0, MyDateColumn), 0)

is the fastest I have found. Works for Month / Minute etc. too

(I did some timing comparisons in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35296, FLOOR/REAL takes almost twice as long, although that is better than convert to VARCHAR(8) which takes 6 times as long ...)

His query is hisDateField >= today @ midnight so the floor/real function is against the result of the getdate() function and not the date value in his table. Would that not help the speed of the query?

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 12:39:34

I did a test in a 1,000,000 iteration loop converting GetDate() using the two methods and FLOOR/REAL is 10% faster

I presume it will only get done once in the calculation

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 12:40:16

"Very cool. I have never seen that."

Not original, saw it on here, but I can't remember who originated it I'm afraid :(

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-01 : 05:09:37

Just a slight abbrevation from Kristens example that I've used for a while and answers the original question...haven't tested the performance but it is at least one less conversion:

SELECT * FROM table WHERE DATEDIFF(day, StartDate, GETDATE()) <= 0

Connoley
Starting Member

1 Post

Posted - 2012-04-03 : 07:10:38

Thanks for the info, I'm after something very similar but I want all rows that have a date of today would I do that with:

Select * from table where datediff(day, startdate, Getdate()) = 0

Regards
Gary

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-03 : 07:53:39

Yes, but you can not use an index over StartDate column, and all rows in table need to be scanned.

select * from dbo.table1
where startdate >= dateadd(day, datediff(day, '19000101', getdate()), '19000101')
and startdate < dateadd(day, datediff(day, '18991231, getdate()), '19000101')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources