| Author |
 Topic  |
|
|
mk_garg20
Constraint Violating Yak Guru
Australia
343 Posts |
 Posted - 07/28/2004 : 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
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 07/28/2004 : 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
Australia
343 Posts |
Posted - 07/28/2004 : 19:05:00
|
Thanks Timmy!
What about if i want to see current date in "DD-MM-YYYY" format without time.
Thanks
Garg |
|
|
|
timmy
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 07/28/2004 : 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
USA
552 Posts |
Posted - 07/29/2004 : 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
USA
552 Posts |
Posted - 07/29/2004 : 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
Australia
343 Posts |
Posted - 07/29/2004 : 18:19:20
|
| Thanks Friends |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/30/2004 : 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 - 07/30/2004 : 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
Australia
343 Posts |
Posted - 07/30/2004 : 02:37:04
|
Thanks Kristen.
|
|
|
|
drymchaser
Aged Yak Warrior
USA
552 Posts |
Posted - 07/30/2004 : 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? |
Edited by - drymchaser on 07/30/2004 07:33:24 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/30/2004 : 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
United Kingdom
22191 Posts |
Posted - 07/30/2004 : 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
Norway
3241 Posts |
Posted - 08/01/2004 : 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
United Kingdom
1 Posts |
Posted - 04/03/2012 : 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
Sweden
29138 Posts |
Posted - 04/03/2012 : 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" |
|
|
| |
Topic |
|
compare date with current date
