DateAdd time period with Oracle

[omerdurdu]

I am trying to see a week worth of modified data in my oracle database and I have a field contains modifying date.
I wrote the code but the result is strange: It is just using the "day" as a criteria, In other words when I say -7 it is going and finding all data for 7 days but for all years not for the just 7 days for 2002. Lets say today 7th, it is going extracting data from all years (31st to 7th) not just for 2002.
Can someone tell me what is going on in this code:
_______________________________________________________
<cfset weekago=#LSdateFormat(DateAdd('d',-7,now()), &quot;mm/dd/yyyy&quot#&' '&#TimeFormat(Now(), &quot;h:m:ss&quot#>

<cfset today=#LSdateFormat(Now(), &quot;mm/dd/yyyy&quot#&' '&#TimeFormat(Now(), &quot;h:m:ss&quot#>

<cfquery datasource=&quot;datacat1&quot; name=&quot;park&quot;>
SELECT Dataset_ID, modifiedon
from Parkcode_xREF
where modifiedon between '#weekago#' and '#today#'
order by dataset_ID
</cfquery>
______________________________________________________

Thanks for help.

 

[SafariTECHPhoenix]

I am not familiar with Oracle usage - but I do work with SQL2000 and Access and FoxPro. So this may not be correct.

When I do a date comparison with a date in the database I do not use LSdateFormat - this is usually for output, not data comparison.

Dates in DBs are stored in ODBC format ... so you should probably use &quot;weekago=CreateODBCDate(DateAdd('d',-7,now())&quot; instead. If you are only worried about getting the previous 7 days, and the time doesn't really matter, then just use what I gave you - the time is ignored. If you want the time as well, then use CreateODBCDateTime

As I said, this may not work for Oracle - but it seems to work on everything else, so give it a try.

Glen Palmer
SafariTECH
&quot;because it's a jungle out there&quot;

 

[omerdurdu]

Thanks for replying
Actually I have a field in my table called modified date
where date inserted as in LSdateFormat and it is Text type
column. I tried your syntax but it seems giving error, seem didnt like it. This code is working in access but not working in Oracle. But SQL server is same as oracle. I am not sure why this is not working but thanks for your reply.

 

[csteinhilber]

Ummm... two unfortunate things.

a) you're not using a Date type (why not??)... Oracle would be able to compare, add, subtract, and otherwise manipulate a Date data type quite readily directly in a SQL statement.

b) you're not using a workable format for your date as a string. mm/dd/yyyy, while US-friendly, can not be readily compared against other mm/dd/yyyy strings.

consider:
12/01/1940 // December 1st, 1940
would actually sort after
01/01/2002 // January 1st, 2002
not, I'm assuming, the intended result.

You'd want a format like yyyy/mm/dd...

1940/12/01
will sort before
2002/01/01
as expected.

I would definitely change your format. For that matter, I would use the Date data type as well... then you can use a query like:

        SELECT 
                Dataset_ID, modifiedon
        FORM
                Parkcode_xREF
	WHERE
		modifiedon > ADD_MONTHS(SYSDATE,-1)

But, baring that, you can use the SQL TO_DATE function, which translates a date string into a date type... though it's not entirely reliable.

        SELECT 
                Dataset_ID, modifiedon
        FORM
                Parkcode_xREF
	WHERE
		TO_DATE(modifiedon,'MM/DD/YYYY','NLS_DATE_LANGUAGE = American') > ADD_MONTHS(SYSDATE,-1)

Hope it helps,
-Carl

 

[r937]

one thing that always puzzles me is why people want to go to so much trouble to convert, reformat, and reconfigure the coldfusion server's date just so that they can use it in a query, when the database server has a perfectly good date itself, along with a host of date functions that are every much as comprehensive as coldfusion's

omerdurdu, forget about those CFSET statements to try to do the date arithmetic yourself

here's all you need:

where modifiedon between sysdate-7 and sysdate

doesn't get much easier than that

rudy

 

[BaconMakinWabbit]

For Oracle use something like this to get the last 7 days...

SELECT somefields
FROM sometable
WHERE somedate BETWEEN (sysdate-7) and sysdate

That will give you all the results for the time period between today and 7 days ago.

And I disagree that Oracle is like SQL Server. Oracle has a lot of Oracle specifc functions just like SQL Server has. There are volumes on Oracle SQL and SQL Server SQL. Hope this helps.

 

[csteinhilber]

WHERE ... BETWEEN ... will only work if the column is of type Date... or at least if the string is in a proper format to be reliably compared.

Omerdurdu has stated his column is neither. Hope it helps,
-Carl

 

[r937]

carl, thanks, you're right, i missed that

omerdurdu, it's really a text column????

whatever reason could you have had for not using a temporal datatype? can you change it?

i guess if it contains valid dates as formatted by coldfusion, you should be okay, but you'll have to cast the column in order to do any date arithmetic on it...