Menu
The following query:
select cdate from rprt where cdate <= TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';
return: 2013/04/27-10:06:26:794 as stored in the table.
I want to get the date only as : 27-04-2013 and get the number of days between the resul tdate and sysdate.
483
In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column. (In our example, we use a column of the timestamp data type.)
The function works with a set of two arguments, an input date and the name of the part that has to be extracted from it. However, datepart() function works in SQL Server, Oracle, and Azure SQL databases only.
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
TIMESTAMP is the same as DATE , except it has added fractional seconds precision. The biggest difference: DATE is accurate to the second and doesn't have fractional seconds. TIMESTAMP has fractional seconds.
Use the function cast() to convert from timestamp to date
select to_char(cast(sysdate as date),'DD-MM-YYYY') from dual;
For more info of function cast oracle11g http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm#SQLRF51256
144
This is exactly what TO_DATE() is for: to convert timestamp to date.
Just use TO_DATE(sysdate) instead of TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS').
SQLFiddle demo
UPDATE:
Per your update, your cdate column is not real DATE or TIMESTAMP type, but VARCHAR2. It is not very good idea to use string types to keep dates. It is very inconvenient and slow to search, compare and do all other kinds of math on dates.
You should convert your cdate VARCHAR2 field into real TIMESTAMP. Assuming there are no other users for this field except for your code, you can convert cdate to timestamp as follows:
BEGIN TRANSACTION;
-- add new temp field tdate:
ALTER TABLE mytable ADD tdate TIMESTAMP;
-- save cdate to tdate while converting it:
UPDATE mytable SET tdate = to_date(cdate, 'YYYY-MM-DD HH24:MI:SS');
-- you may want to check contents of tdate before next step!!!
-- drop old field
ALTER TABLE mytable DROP COLUMN cdate;
-- rename tdate to cdate:
ALTER TABLE mytable RENAME COLUMN tdate TO cdate;
COMMIT;
SQLFiddle Demo
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
