Rent Cslculation Per week - 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

Old Forums

CLOSED - General SQL Server

Rent Cslculation Per week

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 the
tenancy duration)

eg

From To PaymentDate Rent
2006/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 code

select distinct	start_of_week_starting_sun_date,
		end_of_week_starting_sun_date,
		89 rent
from		dbo.F_TABLE_DATE('2006-01-01', '2006-12-31')

Peter Larsson
Helsingborg, 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') GIVES

Server: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377
The 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 write

select distinct	start_of_week_starting_sun_date,
		end_of_week_starting_sun_date,
		89 rent
from		dbo.F_TABLE_DATE('2006/01/01', '2006/12/31')

select distinct	start_of_week_starting_sun_date,
		end_of_week_starting_sun_date,
		89 rent
from		dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006')

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-05 : 07:29:37

Same problem
Pls execute it ur self

Server: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377
The 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/17
2006-17-09	2006/17/9	2006/17/09
17-09-2006	17/9/2006	17/09/2006
09-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 rent
from		dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006')

Peter Larsson
Helsingborg, 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 rent
from dbo.F_TABLE_DATE(convert(datetime,'01/01/2006', 101), Convert(datetime,'12/31/2006',101))

Harsh Athalye
India.
"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()

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 self

Server: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377
The 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 '/'.

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') GIVES

Server: Msg 242, Level 16, State 3, Procedure F_TABLE_DATE, Line 377
The 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 know

DBCC UserOptions

Madhivanan

Failing 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 Athalye
India.
"Nothing is Impossible"

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-07-05 : 09:34:03

This is it here

textsize 64512
language British
dateformat dmy
datefirst 1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-05 : 09:36:22

>>dateformat dmy

Then the function wont work until you input in dmy format

Madhivanan

Failing 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 in

select distinct	start_of_week_starting_sun_date,
		end_of_week_starting_sun_date,
		89 rent
from		dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006')

However

select distinct	start_of_week_starting_sun_date,
		end_of_week_starting_sun_date,
		89 rent
from		dbo.F_TABLE_DATE('1/1/2006', '12/31/2006')

should work. Unless something else is wrong.

Peter Larsson
Helsingborg, 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 in
select distinct start_of_week_starting_sun_date,
end_of_week_starting_sun_date,
89 rent
from dbo.F_TABLE_DATE('Jan 1, 2006', 'Dec 31, 2006')
However
select distinct start_of_week_starting_sun_date,
end_of_week_starting_sun_date,
89 rent
from dbo.F_TABLE_DATE('1/1/2006', '12/31/2006')
should work. Unless something else is wrong.

Peter Larsson
Helsingborg, Sweden

The error is not due to conversion from the passed in value but date string defined in the Function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 09:55:36

Aha!

So changing

IF  @LAST_DATE > '9997-12-31'	begin

IF  @LAST_DATE > 'Dec 31, 9997'	begin

is the solution?

Thanks khtan!

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 10:04:08

quote:
Originally posted by Peso

Aha!

So changing
IF @LAST_DATE > '9997-12-31' begin
to
IF @LAST_DATE > 'Dec 31, 9997' begin
is the solution?

Thanks khtan!

Peter Larsson
Helsingborg, Sweden