Home » SQL & PL/SQL » SQL & PL/SQL » Comparing date and string with SQL where clause (Oracle 9i)
Comparing date and string with SQL where clause [message #340668] Wed, 13 August 2008 13:47 Go to next message
saifora21
Messages: 7
Registered: August 2008
Junior Member
Hi,

I have a column which stores date as strings in YYYY/MM/DD-HH:MM:SS:LLL format. I store another date as string in YYYY/MM/DD format in a string variable in my program. I need to compare these two dates with sql where clause.

Column in the table - TransferredDate - 2007/12/14-01:52:30:098
My string variable - sDate - 2008/08/10

My problem is how do I cmpare two dates which are stored as strings using a sql where clause. Since I use proprietary programming language, I cannot make use of Oracle functions like to_date() etc. I can only use the where clause.

Any help would be appreciated.
Re: Comparing date and string with SQL where clause [message #340670 is a reply to message #340668] Wed, 13 August 2008 13:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
where TransferredDate like sdate||'%'
Re: Comparing date and string with SQL where clause [message #340672 is a reply to message #340668] Wed, 13 August 2008 13:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I have a column which stores date as strings in YYYY/MM/DD-HH:MM:SS:LLL format. I store another date as string in YYYY/MM/DD format in a string variable in my program. I need to compare these two dates with sql where clause.

A stupendously bad idea. Store dates as dates. Why on earth would you store dates as strings?
Re: Comparing date and string with SQL where clause [message #340689 is a reply to message #340672] Wed, 13 August 2008 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why on earth would you store dates as strings?

Because bl... software dictates it to be rdbms independent.

Regards
Michel
Re: Comparing date and string with SQL where clause [message #340694 is a reply to message #340668] Wed, 13 August 2008 16:02 Go to previous messageGo to next message
saifora21
Messages: 7
Registered: August 2008
Junior Member
I understand that storing date as string is a bit awkward, but unfortunately I don't have control over the database.

Is the following query correct technically -

select Transferreddate from table where Transferreddate > '2008/08/10';

In the result I get all the dates greater than 2008/08/10 including 2008/08/10.
Re: Comparing date and string with SQL where clause [message #340696 is a reply to message #340694] Wed, 13 August 2008 16:38 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I understand that storing date as string is a bit awkward,

Nope, it's not a bit awkward, it is simply poor design and will guarantee problems in the future.

Quote:
but unfortunately I don't have control over the database.
Then maybe you should have pointed this out in your original question.

Quote:
Is the following query correct technically -

select Transferreddate from table where Transferreddate > '2008/08/10';


Not if you want the values treated as strings it's not. Look into the to_date function (for both sides of the predicate)

Previous Topic: remove duplicates
Next Topic: Number of Months between two different Years
Goto Forum:
  


Current Time: Mon Dec 05 11:13:03 CST 2016

Total time taken to generate the page: 0.17807 seconds