Strange results using between on dates

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

Strange results using between on dates

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2010-12-02 : 17:35:53

My where statement works fine when I use =

Where convert(varchar(10),dateadd(hh,-6,plannedeventtimeline.starttime),110) = convert(varchar,DateAdd(Day,-1,getdate()) ,110)

But I want to have an expanded range, 2 days back from current and 4 days into the future.

I get really bizarre dates back when I use the between statement i.e. different years, dup records etc.

Where convert(varchar(10),dateadd(hh,-6,plannedeventtimeline.starttime),110) between convert(varchar,DateAdd(Day,-1,getdate()) ,110) and (varchar,DateAdd(Day,4,getdate()) ,110)

What am I missing? Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 17:51:02

This:
convert(varchar(10),dateadd(hh,-6,plannedeventtimeline.starttime),110)

would only go back 6 hours which are then stripped from the formatted date per your conversion, then you are comparing the varchar results..might have something to do with it.

Why are you converting the dates to varchar ?

Sample data and sample results would help get an answer faster

Poor planning on your part does not constitute an emergency on my part.

jmersing
Yak Posting Veteran

57 Posts

Posted - 2010-12-02 : 18:01:06

I only converted them because I thought I had to to get it to return in a format like 12-01-2010. And the fact that it is in GMT time so I thought it had to be coverted to return properly.

I'm not sure how to post a sample of my results properly but I'll try.

Wein StartDate EndDate StartTime EndTime ISOVERTIME Org

10056697 12/1/2010 12/1/2010 16:30:00 18:30:00 0 RPH OH2
10057387 12/1/2010 12/1/2010 9:00:00 11:00:00 0 RPH TX
10057387 12/1/2010 12/1/2010 11:00:00 11:15:00 0 RPH TX
10057387 12/1/2010 12/1/2010 11:15:00 12:30:00 0 RPH TX
10057387 12/1/2010 12/1/2010 12:30:00 13:00:00 0 RPH TX
10057387 12/1/2010 12/1/2010 13:00:00 13:15:00 0 RPH TX
10057387 12/1/2010 12/1/2010 13:15:00 13:30:00 0 RPH TX

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 18:07:40

Is that the source or the desired results?

Give sample source data (and datatypes)...are your dates and times stored separately? are they stored as datetime fields?

if not stored as datetime,...the sorting will be based on character, not numeric ordering so 10/1/2010 would be sorted before 01/1/2010 (for example).

if they are stored as datetime, leave the conversion for the results and not in the where clause...(leave the conversion in the select, but not the where clause)

Poor planning on your part does not constitute an emergency on my part.

jmersing
Yak Posting Veteran

57 Posts

Posted - 2010-12-02 : 18:14:49

They are Datetime datatypes and what I posted would be the desired result. This is an example of what the field looks like if I dont attemp to convert it. 2011-03-07 22:00:00.000

I understand what you meant by the where clause but that did not seem to work either for some reason I used this : Where plannedeventtimeline.starttime >= DateAdd(Day,-2,getdate())
I believe that is what you meant, unless I have this wrong also?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 18:55:44

Please provided some source data and some actual results or something.. I can't explain why your query doesn't work without your actual source data.

as far as your query...same thing. I don't know what column values starttime has in it, so I can't answer the reason.

use this as sample, and replace with dates/times that are giving you trouble. I can't see why your where clause won't work unless "starttime" does not contain an actual date in it.


Create table #foo (
Wein  varchar(12) not null,
StartDate datetime not null,
EndDate datetime not null)

INSERT INTO #foo
SELECT '10056697'	,'12/01/2010 16:30:00'	,'12/01/2010 18:30:00' UNION
SELECT '10057387'	,'12/01/2010 09:00:00'	,'12/01/2010 11:00:00' UNION 
SELECT '10057387'	,'12/01/2010 11:00:00'	,'12/01/2010 11:15:00' UNION
SELECT '10057387'	,'12/01/2010 11:15:00'	,'12/01/2010 12:30:00' UNION
SELECT '10057387'	,'12/01/2010 12:30:00'	,'12/01/2010 13:00:00' UNION 
SELECT '10057387'	,'12/01/2010 13:00:00'	,'12/01/2010 13:15:00' UNION
SELECT '10057387'	,'12/01/2010 13:15:00'	,'12/01/2010 13:30:00'



Select StartDate
		,Enddate
		,StartDate2 = convert(varchar(10),startdate,110)
		,EndDate2 = convert(varchar(10),enddate,110)
		,Starttime = convert(varchar(10),startdate,108)
		,endtime = convert(varchar(10),enddate,108)
FROM #foo
Where startdate >= dateadd(d,-2,getdate())


Drop table #foo

Poor planning on your part does not constitute an emergency on my part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-02 : 19:30:38

Maybe this link will help you prepare you data in a consumable format:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources