SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 compare date with current date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 07/28/2004 :  18:42:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 07/28/2004 :  18:50:36  Show Profile  Visit timmy's Homepage  Reply with Quote
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.
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 07/28/2004 :  19:05:00  Show Profile  Reply with Quote
Thanks Timmy!
What about if i want to see current date in "DD-MM-YYYY" format without time.
Thanks
Garg
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 07/28/2004 :  19:21:17  Show Profile  Visit timmy's Homepage  Reply with Quote
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.
Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 07/29/2004 :  09:27:38  Show Profile  Reply with Quote
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".
Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 07/29/2004 :  09:29:32  Show Profile  Reply with Quote
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'))))
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 07/29/2004 :  18:19:20  Show Profile  Reply with Quote
Thanks Friends
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/30/2004 :  01:57:12  Show Profile  Reply with Quote
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
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 07/30/2004 :  02:29:52  Show Profile  Reply with Quote
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'
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

Australia
343 Posts

Posted - 07/30/2004 :  02:37:04  Show Profile  Reply with Quote
Thanks Kristen.
Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 07/30/2004 :  07:30:54  Show Profile  Reply with Quote
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?

Edited by - drymchaser on 07/30/2004 07:33:24
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/30/2004 :  12:39:34  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/30/2004 :  12:40:16  Show Profile  Reply with Quote
"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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/01/2004 :  05:09:37  Show Profile  Reply with Quote
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
Go to Top of Page

Connoley
Starting Member

United Kingdom
1 Posts

Posted - 04/03/2012 :  07:10:38  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/03/2012 :  07:53:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000