A Strange SQL Query Problem - 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

A Strange SQL Query Problem

Author

Topic

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 06:19:39

Hello guys

Senerio: Users define Events with their starting and Ending date.
I have a table having these records

S.no sdate edate heading
01 4/10/2009 11:13:30 PM 5/25/2009 11:13:30 PM Event1
02 5/22/2009 11:13:30 PM 5/24/2009 11:13:30 PM Event2

Now i am querying the data with this query to get the records and these reccords will only show between their start and end date.

select * from event
where sdate <=getdate()
and edate >=getdate()

But this query only returns one record having Edate = 5/25/2009

Note: Today is = 24-5-2009

Any suggestion

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-24 : 06:26:50

Please consider that your getdate() gives not date only, it gives date and time.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-24 : 06:30:05

use
dateadd(dd,0, datediff(dd,0, getdate()))
instead of getdate()

No, you're never too old to Yak'n'Roll if you're too young to die.

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 06:44:36

thanx bRO IF YOU CAN explain me about this

dateadd(dd,0, datediff(dd,0, getdate()))
instead of getdate()

i will be very thankfull to you then

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 06:56:03

What do you want the query to return?
Rows where the current datetime is between the start and end date? If so then you need to include the time

where getdate() between sdate and edate
If you want rows using midnight rather than the datetime then

where convert(varchar(8),getdate(),112) between sdate and edate
or (I prefer the former where performance isn't an issue)
where dateadd(dd,0, datediff(dd,0, getdate())) between sdate and edate

This is assuming sdate and edate are datetimes
From your question this might not be the case and be why you might be missing rows

dateadd(dd,0, datediff(dd,0, getdate()))
datediff(dd,0, getdate())
Is the number of days from 0 (= 1 jan 1900)
The other bit adds that to 0 - giving todays date without the time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 07:15:38

well sdate and edate are datetime datatype

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 07:21:41

Then your query should have returned everything starting before and ending after the current datetime.
Is that what you wanted?
Is there something special about 11:13:30 as that's when your examples start and end?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 08:15:03

it's working fine with this

select * from jadt
where datet <=dateadd(dd,0, datediff(dd,0, getdate()))
and datef >=dateadd(dd,0, datediff(dd,0, getdate()))

Actually we want to show only those records having start date equal to or less then todate and their enddate is greater then or equal to todate in other words they are expired.

i.e i add an event with start date 10-05-2009 and end date 24-05-2009
so i want to show this event right now on main page as it's starting date is 10-05-2009 and it's ending date is 24th of May.

But this query was't getting this record

select * from jadt
where datet <=getdate()
and datef >=getdate()

But this is one did it work

select * from jadt
where datet <=dateadd(dd,0, datediff(dd,0, getdate()))
and datef >=dateadd(dd,0, datediff(dd,0, getdate()))

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 08:30:54

well even if i have these records

1 2009-05-22 00:00:00.000 2009-05-25 00:00:00.000 event1
2 2009-03-10 00:00:00.000 2009-05-24 00:00:00.000 event2
3 2009-06-20 00:00:00.000 2010-05-22 00:00:00.000 event3

i just want my query to returned everything starting before or equal to and ending after or equal to the current datetime.

that's it

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 09:17:26

Then you want your first query - or more simply

select * from jadt
where getdate() beteen datet and datef

Assuming datet is the from date and datef the to date.

The query you have that you say is working
select * from jadt
where datet <=dateadd(dd,0, datediff(dd,0, getdate()))
and datef >=dateadd(dd,0, datediff(dd,0, getdate()))

gets rows with datet before midnight this morning and datef after midnight this morning not the current datetime.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 09:46:26

so how can i fix this then
as
if i have this record in my table

1 2009-05-22 00:00:00.000 2009-05-25 00:00:00.000 event1

then this query works

select * from jadt
where datet <=dateadd(dd,0, datediff(dd,0, getdate()))
and datef >=dateadd(dd,0, datediff(dd,0, getdate()))

but if i have something like this

2 2009-05-22 23:13:30.000 2009-05-24 23:13:30.000 event2

and running this query again it's not working

i just need to have data <= todate and >= todate that's it any suggestion
Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-24 : 09:53:06

[code]
select *
from jadt
where datef >= dateadd(day, datediff(day, 0, getdate()), 0)
and datet <= dateadd(day, datediff(day, 0, getdate()), 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 09:56:20

getdate() = 20090524 14:48 (ish at the moment)
dateadd(dd,0, datediff(dd,0, getdate())) = 20090524 00:00

where '20090524 00:00' between datet and datef
(and you can run that to test)
Should give both those rows.

Try running
dateadd(dd,0, datediff(dd,0, getdate()))

Maybe your machine datetime is wrong

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 09:57:46

Or maybe you are a day later than me - that would account for it.

suspect you need
select * from jadt
where getdate() between datet and datef

instead of
select * from jadt
where dateadd(dd,0, datediff(dd,0, getdate())) between datet and datef

Actually that still wouldn't give the second row.
What query do you expect to give that result.
Give it with hard coded dates e.g.

where '20090524 00:00' >= '2009-05-22 23:13:30.000'
and '20090524 00:00' <= '2009-05-24 23:13:30.000'

also give your current machine datetime
Just writing it like that should show you where you have the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 10:47:09

thanx Bro any suggestion for mysql ? it's working fine with ms sql can you tell me what are the functions to handle this issue in mysql ?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 10:57:04

Have a look here
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

What did you end up with?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

urheart
Starting Member

8 Posts

Posted - 2009-05-24 : 11:09:06

it's working fine for me

select * from jadt
where datet <=getdate()
and datef >=dateadd(dd,0, datediff(dd,0, getdate()))

thanx to you and kahan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources