Converting TSQL "Where" clause to PLSql

[rodeomount]

Can someone help me with converting the following where clause in TSQL to PLSQL? I need to pull records where the date (in varchar()) is within the last 2 months.




WHERE (IMS_UDT_TS > convert(varchar(26), DATEADD(mm, - 2, getdate()), 21)) AND (IMS_UDT_TS < convert(varchar(26), getdate(), 21))

 

[jimirvine]

OK, just to clarify, you have a column of datatype varchar, which holds a string that represents a date. You want to build a where clause based on this column that only shows rows within the last 2 months from today's date. Is that correct?
Couple of questions.
1. What is the name of the 'date' column? (I can't tell from the code that you posted)
2. What is the format of the 'date'?
3. Do you have the ability to change the datatype of this 'date' column to an actual date datatype?
4. You are not looking for PL/SQL, you are just looking for Oracle's 'flavour' of SQL

 

[rodeomount]

The field name is "IMS_UDT_TS" and holds a string that looks like "2007-11-18-10.55.37.000000". I need a where clause that will retrieve the records with this value between todays date and 2 months prior.


I'm using this inside a DTS package which imports records from an oracle database.


Many Thanks,
Dan

 

[jimirvine]

OK, to convert the string into a date
use

to_date(substr(ims_udt_ts,1,19),'yyyy-mm-dd-hh:mi:ss')

Alternatively you might want to look up the syntax for to_timestamp (as the data you provided implies fractional seconds which only timestamp (and related subtypes and supertypes contain)
To find out if it is between now and 2 months ago

Between add_months(sysdate,-2) and sysdate

However I strongly reccommend that you speak to whoever is in charge of the db and tell them that storing dates as strings is just ... a very very bad idea

 

[rodeomount]

Would it be possible to set it up so that I could convert the sysdate to a string for todays date and today - 2 months and set the where clause as such

where ims_udt_ts between stringvariable2monthsago and stringvariabletoday

 

[Welshbird]

Rodeomount, that is exactly the code that jim provided.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[rodeomount]

I'm getting an error stating "Hour must be between 1 and 12

 

[Welshbird]

I think you may need to change the convert:

to_date(substr(ims_udt_ts,1,19),'yyyy-mm-dd-hh24:mi:ss')

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Try

to_date(substr(ims_udt_ts,1,19),'yyyy-mm-dd-hh24:mi:ss')