Problem Selecting between dates in SQL Server 2005

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 2005 Forums

Transact-SQL (2005)

Problem Selecting between dates in SQL Server 2005

Author

Topic

Stig Christian Aske
Starting Member

6 Posts

Posted - 2008-07-08 : 04:36:39

I have a problem with dates.

I have a table with DateStart and DateEnd, both "smalldateandtime". The values in the fields may be "01.07.2008" or "01.07.2008 07:30:00"

What I need is to pick out the records who are within a spesific date. Let´s say I want to pick out all records where 01.08.2008 is in between DateStart and DateEnd.
And also the records of the current day. (DateStart: 01.08.2008 12:00:00 - DateEnd: 01.08:2008 18:00:00)

Remember that some records includes time, some don´t.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-07-08 : 04:48:23

But datetime always includes a time? Are you sure your data is not of some varchar type?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 04:50:41

select * from yourtable where datefield >=StartDate and datefield <=enddate

Stig Christian Aske
Starting Member

6 Posts

Posted - 2008-07-09 : 10:24:31

Yes, that´s what I tried before I posted the topic.

But I seem to get only the Records that starts before the date asked for. The records within the current date (with start and end time) will not show.

Any solutions?

Stig Christian Aske
Starting Member

6 Posts

Posted - 2008-07-09 : 10:25:47

Can I convert the query date to some sort of timestamp or anything?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 11:52:37

quote:
Originally posted by Stig Christian Aske

Yes, that´s what I tried before I posted the topic.

But I seem to get only the Records that starts before the date asked for. The records within the current date (with start and end time) will not show.

Any solutions?

what's the datatype of your datefield? also some sample data from your table might help to illustrate your problem.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-09 : 14:45:01

Just guessing but are you trying to get all the records that happened on a particular date (day)?

If so, then try something like this:

SELECT *
FROM MyTable
WHERE 
	StartDate >= '20080101'
	AND EndDate < '20080102'

EDIT: Typo

buzzi
Starting Member

48 Posts

Posted - 2008-07-09 : 15:45:14

You need to include the complete date not just the date including the time

SELECT *
FROM yourtable
WHERE
StartDate >= '20080107 00:00:00'
AND EndDate < '20080108 23:59:00'

when you specify the EndDate it always try to find records with the enddate + 00:00:00. it does not include the entire day

hope this helps

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-09 : 15:58:45

quote:
Originally posted by buzzi

You need to include the complete date not just the date including the time

SELECT *
FROM yourtable
WHERE
StartDate >= '20080107 00:00:00'
AND EndDate < '20080108 23:59:00'

when you specify the EndDate it always try to find records with the enddate + 00:00:00. it does not include the entire day

hope this helps

I'm not sure if you are replying to me or the OP, but your query will not include records that fall on '20080108 23:59:000'

EDIT: Changed to the last Minute of the day as I forgot we are talking about SmallDateTime not DateTime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources