CODEWITHSUNDEEP
sqldateformat
user823498
user823498

Reputation: 37

query on date column

I have a column (completeddate) that I need to do the following

select *
from orders
where completeddate > '01-01-2013'

The problem is the date in the field looks like this (below)

25-FEB-08 12.00.00.000000000 AM

The format of the column is this

TIMESTAMP(6)

How do I set up my query to return rows where the completeddate is greater than 01-01-2013

Upvotes: 0

Views: 92

Answers (4)

shuang
shuang

Reputation: 246

You need to convert the string '01-01-2013' to a database date object before the comparison. Don't know what is the type of your database, but all databases shall provide a function for converting date objects to and from strings, you can have something like:

where completeddate > TO_DATE('01-01-2013', 'DD-MM-YYYY')

Hope this helps.

Upvotes: 0

JDB
JDB

Reputation: 25865

You haven't specified the DBMS you are using, but nearly all of them store dates as a number. The format that you are seeing is almost certainly the client's interpretation of the date and has nothing to do with how the date is actually stored.

Most DBMSes are able to translate standard string representations of dates into a numeric date value. The likely problem is that you aren't using a standard date format. You should either use '2013-01-01' or '01/01/2013'. In my experience, YYYY-MM-DD (ISO 8601 and RFC 3339) is the least ambiguious and most supported format. If you strongly prefer MM/DD/YYYY (a primarily North American representation) then you usually need to use backslashes.

Upvotes: 1

ricardorios
ricardorios

Reputation: 376

did you try this?

SELECT CAST(your_timestamp_field AS DATETIME) FROM orders WHERE completeddate > '2013-01-01'

Upvotes: -1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try this

select *
from orders
where completeddate > CONVERT(DATETIME, '01-01-2013', 105)

105 for dd-MM-yyy

Upvotes: 1

Related Questions