Hello,
My issue is as follows:
1. I have one variable of type DATE, which I assign the value of SYSDATE
mydatevar DATE:= SYSDATE;
2. I want to find *"today"*, truncated to
DAY
TRUNC (mydatevar, 'DD')
TRUNC function returns DATE datatype. So I will receive in point 2 for example *'2010-01-13 00:00:00'*.
3. I want to assign the value from point 2 to a variable of type TIMESTAMP
mytimestampvar TIMESTAMP := mydatevar;
which implicitly will convert the DATE variable to TIMESTAMP.
Problem: During the conversion (both implicit and explicit conversion with a format mask) I lose the "00" hours and "00" minutes and receive something like this: "10-JAN-13 *12*.00.00.000000000 AM".
Question: How can I convert from DATE to TIMESTAMP keeping hours and minutes zeros?
Why I need this conversion: I have a table with a column "column1" TIMESTAMP(0) and I would like to take only those rows from the table, where "column1" is in range from today 12 o'clock in the morning till now (whatever hour it is).
NLS characteristics of the database:
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.4.0
Session parameters are the same.
DBTIMEZONE is "+02:00".