Author |
Topic |
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-05 : 05:17:17
|
Tenancy Agreement = 2006/01/01 to 2006/01/31 (end of year)The are paying £89.00 per week How do I show(table detailing Payment Day )/caluclate when next the will pay me based on weekly payment In summary I need to input Start and end date then SQL will display everything (detailed payment plan for thetenancy duration)eg From To PaymentDate Rent2006/01/01 2006/01/08 ?? 89.00. . ?? ?? . . ?? ?? 2006/01/31 ?? ?? |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 05:45:40
|
quote: Originally posted by OBINNA_EKE Tenancy Agreement = 2006/01/01 to 2006/01/31 (end of year)
Very short year to me! Just kidding.Using the function from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519 , use this codeselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('2006-01-01', '2006-12-31') Peter LarssonHelsingborg, Sweden |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-05 : 07:17:30
|
select * from dbo.F_TABLE_DATE ('2006-07-05','2006-09-05') GIVESServer: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 07:25:11
|
How about your date format? First date cannot be later than second date.You might writeselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('2006/01/01', '2006/12/31') orselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006') Peter LarssonHelsingborg, Sweden |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-05 : 07:29:37
|
Same problem Pls execute it ur selfServer: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 08:04:11
|
Yes, I have tried every example here and it works very well for me. Maybe because I have SET DATEFORMAT ymd, as common in Sweden.Which is the common date format in the country where you live?How do you write for example September 17, 2006 with numbers and dashes only?2006-09-17 2006/9/17 2006/09/172006-17-09 2006/17/9 2006/17/0917-09-2006 17/9/2006 17/09/200609-17-2006 9/17/2006 09/17/2006 If you run this piece of code, do you still get the same error?select distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006') Peter LarssonHelsingborg, Sweden |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-05 : 08:19:29
|
or how about explicitly converting to some date format before passing it to a function using convert like:select distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE(convert(datetime,'01/01/2006', 101), Convert(datetime,'12/31/2006',101))Harsh AthalyeIndia."Nothing is Impossible" |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-05 : 08:33:05
|
"from dbo.F_TABLE_DATE(convert(datetime,'01/01/2006', 101), Convert(datetime,'12/31/2006',101))"You can't do this. You have to convert to a variable then pass the variable into the F_TABLE_DATE() KH |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-05 : 08:52:49
|
quote: Originally posted by OBINNA_EKE Same problem Pls execute it ur selfServer: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Set the date format set dateformat dmy or (as Peter suggested)set dateformat ymd and it should be fine.Alternatively modify F_TABLE_DATE and change all date in string to YYYYMMDD format from YYYY-MM-DD and YYYY/MM/DD. Remove the dash '-' and '/'. KH |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-05 : 09:00:13
|
quote: Originally posted by OBINNA_EKE select * from dbo.F_TABLE_DATE ('2006-07-05','2006-09-05') GIVESServer: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
What is the date format of the server?Run this and knowDBCC UserOptionsMadhivananFailing to plan is Planning to fail |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-05 : 09:12:09
|
quote: Originally posted by khtan "from dbo.F_TABLE_DATE(convert(datetime,'01/01/2006', 101), Convert(datetime,'12/31/2006',101))"You can't do this. You have to convert to a variable then pass the variable into the F_TABLE_DATE() KH
Oh...yes, you are right.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-05 : 09:34:03
|
This is it heretextsize 64512language Britishdateformat dmydatefirst 1quoted_identifier SETarithabort SETansi_null_dflt_on SETansi_defaults SETansi_warnings SETansi_padding SETansi_nulls SETconcat_null_yields_null SET |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-05 : 09:36:22
|
>>dateformat dmyThen the function wont work until you input in dmy formatMadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 09:43:03
|
But it is strange that Jan 1, 2006 and Dec 31, 2006 does not work as inselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006') Howeverselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('1/1/2006', '12/31/2006') should work. Unless something else is wrong.Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-05 : 09:45:30
|
quote: Originally posted by Peso But it is strange that Jan 1, 2006 and Dec 31, 2006 does not work as inselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006') Howeverselect distinct start_of_week_starting_sun_date, end_of_week_starting_sun_date, 89 rentfrom dbo.F_TABLE_DATE('1/1/2006', '12/31/2006') should work. Unless something else is wrong.Peter LarssonHelsingborg, Sweden
The error is not due to conversion from the passed in value but date string defined in the Function. KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 09:55:36
|
Aha!So changingIF @LAST_DATE > '9997-12-31' begin toIF @LAST_DATE > 'Dec 31, 9997' begin is the solution?Thanks khtan!Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-05 : 10:04:08
|
quote: Originally posted by Peso Aha!So changingIF @LAST_DATE > '9997-12-31' begin toIF @LAST_DATE > 'Dec 31, 9997' begin is the solution?Thanks khtan!Peter LarssonHelsingborg, Sweden
I universalized it and it works with dateformat dmy KH |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-05 : 10:29:46
|
I changed it still does not work#I think the function has a problem |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-07-25 : 08:55:33
|
the function probably doesn't have a problem Have you tried inputting your date in yyyymmdd hh:mm:ss.mmm format?Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|