Retrieving past dates (function)?

Forum|Forum|17 years ago
January 14, 2009
3 replies
1458 views

My query returns the current month and year for my report using the datepart(year, getdate()). How would I retrieve last year or the year before? I can't figure what functions to use. Can anyone help.

SELECT datepart(month, r.name_check_sent) as check_Month,
count(distinct c.case_number) as companies,
count(distinct r.individual_key) as individuals

FROM #request.app.DB2DB#.anc_case c
JOIN #request.app.DB2DB#.anc_individual i on c.case_number = i.case_number
join #request.app.DB2DB#.anc_agency_response r on i.individual_key = r.individual_key
WHERE r.name_check_sent is not null and datepart(year, getdate()) = datepart(year, r.name_check_sent)
group by datepart(month, r.name_check_sent)

Database access

This topic has been closed for replies.

-

-__cfSearching__-

Inspiring

> My method: datepart(year, '2008')

Yes ... but that is still incorrect. DatePart(year, someDate) extracts the year from a _date_ . The way you are using it, 2008 is not a date. MS SQL will interpret 2008 as days from epoch (ie the year 1905). Probably not at all what you want. Try it and see what happens:

select datePart(year, 2008) as ThisIsNot

> <cfqueryparam value="#2008#" cfsqltype="cf_sql_integer">

You must add the proper cfsqltype as well. You can read about how to properly use cfqueryparam in the online documentation. However, there is really no need to hard code the value. Just use the Year() and now() functions.

http://livedocs.adobe.com/coldfusion/8/JavaScriptFcns_02.html

-

-__cfSearching__-

Inspiring

> http://livedocs.adobe.com/coldfusion/8/JavaScriptFcns_02.html

Wrong link. It should be:
http://livedocs.adobe.com/coldfusion/8/htmldocs/functions-pt0_01.html

S

shearakAuthor

Inspiring

I am trying to understand your last sentence.
"However, there is really no need to hard code the value. Just use the Year() and now() functions."

Here is your method which now also gives me the same results. Where would I use the Year() or Now() functions? I just need to get last years data (08), but wouldn't those functions pull current data (09).

datepart(year, r.name_check_sent) = <cfqueryparam value="#2008#" cfsqltype="cf_sql_integer">

Sorry to pick your brain :)

-

-__cfSearching__-

Inspiring

> datepart(year, 2008)

That would return the wrong value. Something like 1905. Besides, if you are going to pass in the year explicitly, you do not need datePart(year,..). You already have it ;-)

WHERE ...
datepart(year, r.name_check_sent) = <cfqueryparam value="#yourYear#" ..>

S

shearakAuthor

Inspiring

Thanks for the reply. I got the same results using your method which is the secure way, because of the cfqueryparam.

Your method: datepart(year, r.name_check_sent) = <cfqueryparam value="#2008#">

My method: datepart(year, '2008') = datepart(year, r.name_check_sent)

D

Dan_Bracuk

Inspiring

datepart year returns an integer. You can simply subtract 1 from it.

Or you could apply a datediff function to getdate()

S

shearakAuthor

Inspiring

Thanks for the reply. I actually just instead of getdate()), put the year 2008. I think subtracting 1 from it will do it also. I will try that as well.

datepart(year, getdate())
datepart(year, 2008)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded