EasyCFM.COM ColdFusion Forums / Oracle Related Issues / getdate / year

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: getdate / year

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

dlackey
11-09-2007 @ 3:06 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

In MS SQL2000, I have a query like so:

SELECT     UID, Completed
FROM         TRNG_YTD_Complete
WHERE     (Valid = 1) AND (YEAR(Completed) = YEAR(GETDATE()))
ORDER BY UID

I have to rewrite this for use in Oracle.  I'm only needing the data for the current year.  We'd liek for this query to work next year so we don't have to keep updating the code.  I dont' know what the equivalent is in Oracle for MSSQL's getdate function.  Can someone help me?

Cheers,
Chip

mquack
11-09-2007 @ 4:34 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

It's not an Oracle solution, but if you're using it in CF, and all you need is the current year, why not just use CF to supply the year?

<cfquery name="foo" datasource="#dsn#">
SELECT     UID, Completed
FROM         TRNG_YTD_Complete
WHERE     (Valid = 1) AND (YEAR(Completed) = #YEAR(now())#)
ORDER BY UID
</cfquery>



www.rachelqueensg.com
Amazon Wish List

dlackey
11-09-2007 @ 5:04 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

Mquack:  I'd think that would work too but it is an Oracle idiosyncrasy.

SOLUTION:

SELECT     UID, Completed
FROM         TRNG_YTD_Complete
WHERE     (Valid = 1) AND (EXTRACT(YEAR FROM Completed) = EXTRACT(YEAR FROM SYSDATE))
ORDER BY UID


Cheers,
Chip

This message was edited by dlackey on 11-9-07 @ 5:05 PM


Website Designed and Developed by Pablo Varando.