My database save the date as this format 5/29/2015 12:07:58.000000 AM. And I got the date input from user as 5/29/2015 12:07:58 format. Can I just compare the day/month/year instead of the whole line in sql command? my database is oracle and format as TIMESTAMP.
anyone have any idea how to do it?
Asked
Active
Viewed 3,072 times
1
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
Lst Patrick
- 123
- 6
- 19
-
1What database are you using and what is the data type of that column? There should not be any format at all. Format is only an issue when dealing with text and dates should be stored as binary dates, not text. If you really do have a format then you're almost certainly doing it wrong. – jmcilhinney May 29 '15 at 01:48
-
oracle and timestamp – Lst Patrick May 29 '15 at 01:50
-
Do you prefer comparing individual day , month, year, or you mean the comparing date+month+year? – Jacky May 29 '15 at 01:51
-
Possible duplicate of http://stackoverflow.com/questions/7433363/how-to-compare-two-date-values-based-only-on-date-part-in-oracle. – ForguesR May 29 '15 at 01:53
-
yeap.. i would like to compare dd/mm/yyyy only @Jacky – Lst Patrick May 29 '15 at 01:54
-
Use `to_char()` or `to_date()`. – Gordon Linoff May 29 '15 at 02:07
2 Answers
2
My database save the date as this format 5/29/2015 12:07:58.000000 AM
No. Date/Timestamps doesn't have any format. Oracle doesn't store the date/timestamps in the format you see. The format you see is only for display purpose. Date is internally stored in 7 bytes which is Oracle's proprietary format.
Can I just compare the day/month/year instead of the whole line in sql command?
Of course you could. You could use TRUNC which truncates the time portion and leaves only date portion and data type remains as date.
For example,
SQL> SELECT TRUNC(SYSTIMESTAMP) my_tmstmp FROM DUAL;
MY_TMSTMP
----------
2015-05-29
And I got the date input from user as 5/29/2015 12:07:58 format.
So, you get the user input in that format as a string. You need to first convert it into DATE using TO_DATE and then compare it.
For example,
WHERE TRUNC(dt_column) < TO_DATE('05/29/2015 12:07:58', 'MM/DD/YYYY HH24:MI:SS')
As I already said, if you want to ignore the time portion, then apply TRUNC.
However, applying TRUNC on the date column would suppress any regular index on that column. From performance point of view, better use a Date range condition.
For example,
WHERE dt_column
BETWEEN
TO_DATE('05/29/2015 12:07:58', 'MM/DD/YYYY HH24:MI:SS')
AND
TO_DATE('05/29/2015 12:07:58', 'MM/DD/YYYY HH24:MI:SS') +1
Lalit Kumar B
- 47,486
- 13
- 97
- 124
0
I presume your record in databases and user input is both Timestamp. You can use TRUNC function to get the date of this value and use this to compare
declare
v_tsdate DATE;
v_txdate DATE;
begin
v_tsdate := TRUNC( CAST('5/29/2015 12:07:58.000000 AM' as DATE) );
v_txdate := TRUNC( CAST('5/29/2015 12:07:58' as DATE) );
--do the comparision and conditions you need with v_tsdate and v_txdate
end;
The reason I convert to DATE format, because TRUNCT(Timestamp) theoretically is TRUNC(CAST(Timestamp AS DATE))
Jacky
- 2,924
- 3
- 22
- 34