April 30, 2008 at 8:40 am
Is there a function to calulate a date? I know the Month and Day, and the year comes from a Report Parameter, so I need to put it all back together and have it as a usable date field that I can calculate against.
April 30, 2008 at 8:47 am
Look at CAST OR CONVERT
CONVERT(DATETIME,'1/1/2000')
April 30, 2008 at 1:23 pm
select dateadd(day, YourDayValue-1, dateadd(month, YourMonthValue-1, dateadd(year, YourYearValue-1900, 0)))
or
select cast(cast(YourMonthValue as varchar(2)) + '/' + cast(YourDayValue as varchar(2)) + '/' +
cast(YourYearValue as char(4)) as datetime)
or
select cast(cast(YourYearValue as char(4)) + right('00' + cast(YourMonthValue as varchar(2)), 2) +
right('00' + cast(YourDayValue as varchar(2)), 2) as datetime)
Those are the three ways I'm familiar with. They all assume your data is numeric. If the month is a name (Mar/Mar./March), then you don't need the inner cast statements in the second one, and the first one and third one won't work at all.
The second one assumes American date formatting, and won't work if you use day/month/year format. The other two work regardless of format.
All four assume 4-digit years.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2008 at 1:53 pm
This is probably the fastest method, because it uses only one date function call:
Select
MyDate = dateadd(month,(12*a.MyYear)-22801+a.MyMonth,a.MyDay-1)
from
(
--Test Data
Select MyYear = 2008, MyMonth = 12, MyDay = 31
) a
Results:
MyDate
-----------------------
2008-12-31 00:00:00.000
(1 row(s) affected)
Here are links of topics devoted to this subjet:
January 6, 2010 at 10:52 am
I am writing a report that I want all open charts for the last 60 days. Not sure how to set it up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply