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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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".
Go to Top of Page

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'))))
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-29 : 18:19:20
Thanks Friends
Go to Top of Page

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
Go to Top of Page

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'
Go to Top of Page

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-30 : 02:37:04
Thanks Kristen.
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -