-
Contributing Member
To_date problem
Hello friends,
SELECT DISTINCT DSL_STK_DATE FROM DSL_STOCK_LEDGER
The output of the above command is
DSL_STK_DATE
28-02-2008
29-02-2008
01-03-2008
02-03-2008
03-03-2008
But the following command displaying unexpected result for the same column. It is showing year portion as 0008. What might be the cause?
SELECT DISTINCT TO_DATE(DSL_STK_DATE, 'DD-MM-YYYY') DSL_STK_DATE FROM DSL_STOCK_LEDGER
DSL_STK_DATE
28-02-0008
29-02-0008
01-03-0008
02-03-0008
03-03-0008
Regards
Babi
-
Expert Member
Re: To_date problem
DSL_STK_DATE is date datatype then no need to use to_date function againg.u still need to use to_date then u need to alter the session using
alter session set nls_date_format = 'dd-mm-yyyy'
SELECT DISTINCT TO_DATE(DSL_STK_DATE, 'DD-MM-YYYY') DSL_STK_DATE FROM DSL_STOCK_LEDGER
other wise use
SELECT DISTINCT TO_char(DSL_STK_DATE, 'DD-MM-YYYY') DSL_STK_DATE FROM DSL_STOCK_LEDGER
or
SELECT DISTINCT TO_DATE(DSL_STK_DATE, 'DD-MM-RRRR') DSL_STK_DATE FROM DSL_STOCK_LEDGER
if u use RRRR in place of yyyy u need to note some points
Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules