Date Range Problems need help - 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

Date Range Problems need help

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-29 : 10:53:31

Good Morning Team:

I am having a problem selecting from a range of date selected by the user using parameter declaration. I want to be able to select the records from ex. Feb 02, 2007 to Feb 05, 2007 list the records that ocurred on those dates.


Declare @Req_Contract_nbr char (8),
@Req_Begin_Day int,
@Req_End_Day int,
@Req_month int,
@Req_year  int

Set @Req_Contract_nbr  = 'GTH00001'
Set @Req_Begin_Day  = '2'
Set @Req_End_Day =  '5'
set @Req_month = '2'
Set @Req_year ='2007'

SELECT a.contract_nbr, SUM(c.dlvry_nom_vol)AS Deliveries, 
MAX(c.beg_eff_date) As Selected_Date,MAX( c.rcpt_dlvry_ind ) AS Delivery_Type
from  TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join  TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) 
and  (c.rcpt_dlvry_ind ='D') and a.contract_nbr= @Req_Contract_nbr 
and Day(c.Beg_eff_date) Between  @Req_Begin_Day and @Req_End_Day
and (month(c.Beg_eff_date)=  @Req_month )
Group by a.contract_nbr


Data Example:

Contract_nbr **  Deliveries ***  Date***    Delivery Type
GTH00001          389231.0       20070202      R
GTH00001          687680.8       20070203      D
GTH00001          608798.9       20070204      D
GTH00001           80980.5       20070205      R

This is the data that I am trying to record.

Thank you again to all of you for all your help.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-11-29 : 11:18:15

[code]WHERE c.Beg_eff_date >= '20070202'
AND c.Beg_eff_date < '20070206'
[/code]

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-29 : 11:28:11

Thank you Ifor.....I cann't use a fix value...I must have the flexibility to choose any day range. The user must have the flexibility to pick any day range. from 1-20 from 10-20 from 5-31 etc....

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-29 : 11:36:49

Yea but instead of breaking out your date like that (which will probably make any indexes on your date field not be used) instead make your variables like this:

Declare @Req_Begin_Day datetime,
@Req_End_Day datetime

Set @Req_Begin_Day = '2/2/2007'
Set @Req_End_Day = '2/5/2007'

Then use those as Ifor has suggested.

WHERE c.Beg_eff_date >= @Req_Begin_Day
AND c.Beg_eff_date < @Req_End_Day

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-29 : 11:46:51

Thank You Van

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-29 : 12:01:21

No problem. Give Ifor the credit. I was just building on what he said.

One thing to note with dates though is the time portion and the use of > or >= and < or <=. You may have to play around to ensure you get what you want. If your datetime field has times other than midnight then you may have to say <2/6/2007 so that you get everything on the 5th. If you said <=2/5/2007 and one of the records was "2/5/2007 7:00am" then it would get lost because <=2/5/2007 means "2/5/2007 00:00:00" (at midnight). You could try to say <=2/5/2007 11:59:59 but you still run the risk of missing records in a very small time frame. It's better to just say <2/6/2007 (with no = sign). You can accomplish it by simply adding 1 day to your end_date variable before you use it in your SELECT or you could say <(@end_date +1).

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-29 : 12:24:42

Hi Van:

I am getting the following Error. Any help in how to fix this error....Thanks!!!!

and (c.rcpt_dlvry_ind ='D') and (a.contract_nbr= @Req_Contract_nbr)
and (c.Beg_eff_date >= @Req_Begin_Date)
and (c.Beg_eff_date< @Req_End_Date)
Group by a.contract_nbr

Server: Msg 242, Level 16, State 3, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(0 row(s) affected)

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-29 : 12:25:28

Thank You Ifor....Thank you for the help...can you help with the Error message? Thanks!!!!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-29 : 13:41:19

It appears that your c.Beg_eff_date field isn't really a datetime field but instead a char field and it has invalid dates in it. That's one good reason to have all datetime fields as a datetime instead of char or varchar. Check the c.Beg_eff_date for bad dates (like 2/30/2004 or 4/5/199 or anything else that's not a real date).

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-29 : 15:12:37

You GUYS ARE THE BEST !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-29 : 15:19:59

LOL, did you change the datatype to datetime or just fix the bad date record(s). If you left it as char but fixed the bad date record(s), it's almost guaranteed to happen again. Also, if you don't change it to datetime, you can't use a date range. Think of it like this "11/1/07" comes before "2/1/07" if it's a char field because the leading 1 in 11 is less than the 2 in the 2nd date. Now "11/1/07" comes after "02/1/07" since the second date now has a leading 0. Then you have to factor in days and whether or not they have a leading 0 and all. You need to change that field to datetime if you haven't already.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources