SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Rent Cslculation Per week		  Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

OBINNA_EKE
Posting Yak Master

234 Posts
Posted - 07/05/2006 :  05:17:17  Show Profile
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

Sweden
29138 Posts
Posted - 07/05/2006 :  05:45:40  Show Profile  Visit SwePeso's Homepage
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
Edited by - SwePeso on 07/05/2006 05:53:02
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts
Posted - 07/05/2006 :  07:17:30  Show Profile
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  07:25:11  Show Profile  Visit SwePeso's Homepage
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')
or
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
Edited by - SwePeso on 07/05/2006 07:28:02
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts
Posted - 07/05/2006 :  07:29:37  Show Profile
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  08:04:11  Show Profile  Visit SwePeso's Homepage
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
Edited by - SwePeso on 07/05/2006 08:06:33
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 07/05/2006 :  08:19:29  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 07/05/2006 :  08:33:05  Show Profile
"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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 07/05/2006 :  08:52:49  Show Profile
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 '/'.


KH
Edited by - khtan on 07/05/2006 08:55:59
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 07/05/2006 :  09:00:13  Show Profile  Send madhivanan a Yahoo! Message
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 07/05/2006 :  09:12:09  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts
Posted - 07/05/2006 :  09:34:03  Show Profile
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 07/05/2006 :  09:36:22  Show Profile  Send madhivanan a Yahoo! Message
>>dateformat dmy

Then the function wont work until you input in dmy format

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  09:43:03  Show Profile  Visit SwePeso's Homepage
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
Edited by - SwePeso on 07/05/2006 09:43:41
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 07/05/2006 :  09:45:30  Show Profile
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.

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  09:55:36  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 07/05/2006 :  10:04:08  Show Profile
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


I universalized it and it works with dateformat dmy

KH
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts
Posted - 07/05/2006 :  10:29:46  Show Profile
I changed it still does not work#

I think the function has a problem
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 07/25/2006 :  08:55:33  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000