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.
| Author |
Topic |
|
solent
Starting Member
33 Posts |
 Posted - 2005-11-08 : 07:50:36
|
I have the following querySELECT dbo.tbl_investment_H_Case1.*, h_MATURITY - ReportDate / 30.4167 AS [AS Date]FROM dbo.tbl_investment_H_Case1 when i run it in query analyzer i get the follwoing error.invalid operator for data type. Operator equals divide, type equals smalldatetime.Both h_maturity and Reportdate are smalldatetime.Any ideas how i can do that? |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-11-08 : 07:54:01
|
| Try to use datediff function. For more information, please check Books online.regardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-08 : 08:09:45
|
| Explain what you are trying to do by giving some sample data and the result you wantMadhivananFailing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-08 : 08:33:16
|
| What the heck is that expression trying to calculate?Can you tell me what June 12, 1985 divided by 234.23 should be? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-08 : 08:38:36
|
| ReportDate/30.4167??? Uhmm...have you looked at any of the built-in datetime functions (datediff, dateadd...)? And why 30.4167? Is that supposed to handle a solar year rather than a calendar year? |
|
|
|
solent
Starting Member
33 Posts |
Posted - 2005-11-08 : 09:43:15
|
| well i am calculating the band of an asset. the band has a standar value based on the above caculation. for example.maturity date of the asset - report date (report date is always the last date of the month as this report is monthly) / 30.4167 (which the average days per month of a year dont quite remeber how this came out since i develope the source of code of this application a year back)the following runs fine in vba = (h_maturity - ReportDate) / 30.4167 < = 1sample data.H_maturity Report Date H_band04/12/2012 30/09/2005 (to be calculated based on the above formule h_maturity - reportdate / 30.4167)So the final output of h_band is based on a lot of cases (another problem that i have but i think i found a solution around it)so i have an update query.UPDATE dbo.tbl_investment_H_Case1SET h_band = 'up to 1 month'WHERE (h_MATURITY - ReportDate / 30.4167 <= N'1')the above h_band will have a lot of cases based on the where statment.Anyway i think i got it with the DATEDIFF(day, ReportDate, h_MATURITY) / 30.4167 which for the sample data i gave above this will return 86.2026452yes the datediff worked. thanks guys for your replies |
|
|
|
|
|
|
Problem with Dates