DATE FORMAT

building semantic layer
1

Hi All,

I have a report in which one column is in this format

'MM-DD-YYYY HH24:MI:SS'

I need to convert that into

 d/m/yyyy

TO_CHAR (RYM91.glh_to_date@kllc_READ_RyM_NEM (e.end_t),
‘MM-DD-YYYY HH24:MI:SS’)
AS CHARGE_DATE,– charge date format should be d/m/yyyy

RUC :us: (BOB member since 2010-05-03)

2

What database are you using ?

BO_Chief :us: (BOB member since 2004-06-06)

3

Database is Oracle

RUC :us: (BOB member since 2010-05-03)

4

Be careful, there is a difference between formatting a date and converting a date. If you convert a date to a string with the desired format, then what you have is a string. That means it may have sorting issues, and you won’t be able to use this value as input to any date functions.

The Oracle function needed to convert from date to string is to_char().

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Error : date format not recognized

I tried both but no luck


 to_char(LIEBEL.S_PASSET_L.ARIB_26 ,'d/m/yyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'

  to_char(to_date(LIEBEL.S_PASSET_L.ARIB_26 ,'DD-MMM-YY HH24:MI:SS:FF'),'d/m/yyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'

RUC :us: (BOB member since 2010-05-03)

6

Count the y’s in your year format. :wink:

That being said, this:

to_char(LIEBEL.S_PASSET_L.ARIB_26 ,'d/m/yyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'

… is not going to work. You are comparing strings in a between fashion, and that is not equivalent to comparing dates. If you want to remove the time element from your date in order to use it in a condition, use the trunc() function instead.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

Still the same error : date format not recognized

Cause: A date specification contained an invalid format code.

Action: Check that only valid date format codes are specified.


  to_char(to_date(LIEBEL.S_PASSET_L.ARIB_26 ,'DD-MMM-YY HH24:MI:SS:FF'),'d/m/yyyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'
to_char(LIEBEL.S_PASSET_L.ARIB_26 ,'d/m/yyyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'

RUC :us: (BOB member since 2010-05-03)

8 
trunc( LIEBEL.S_PASSET_L.ARIB_26 , 'd/m/yyyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'

Something wrong in my code

RUC :us: (BOB member since 2010-05-03)

9

Apart from what Dave said, see the date formats you try to compare in your second code.
The left hand side operand is in d/m/yyyy format and the right hand side operand is in d-m-yyyy format.

Jansi :india: (BOB member since 2008-05-12)

10

this time I tried this but no luck. Not sure what I am missing.
Error : Date format not recognized


trunc( to_date(LIEBEL.S_PASSET_L.ARIB_26 ,'DD-MMM-YY HH24:MI:SS:FF'),'d-m-yyyy') BETWEEN  '1-1-2012'  AND  '31-1-2012'

RUC :us: (BOB member since 2010-05-03)

11

Perhaps you did not realize it, but the trunc() function in my earlier is a link to another site with documentation on how to use the function. You’re not using it correctly.

Is your column a date or datetime or character stored as date? That’s the first question. I have been assuming it’s a date since you said you wanted to reformat a date.

Date formats should not matter when comparing dates to dates. What you seem to be running into is that your date on the left has a time, and the dates on the right do not. The trunc() command takes care of that.

trunc(LIEBEL.S_PASSET_L.ARIB_26) BETWEEN  '1-1-2012'  AND  '31-1-2012'

However, in order for this to work, your database has to apply an implied conversion on the right side of your expression too. That means that ‘1-1-2012’ and ‘31-1-2012’ also have to be valid date expressions. If they’re not, then you must convert them from character to date.

trunc(LIEBEL.S_PASSET_L.ARIB_26) BETWEEN  to_date('1-1-2012','DD-MM-YYYY')  AND  to_date('31-1-2012','DD-MM-YYYY')

If you’re looking for all of 2012 then you can also try this:

to_char(LIEBEL.S_PASSET_L.ARIB_26,'YYYY') = '2012'

or

trunc(LIEBEL.S_PASSET_L.ARIB_26,'YYYY') = to_date('01-01-2012','DD-MM-YYYY')

Dave Rathbun :us: (BOB member since 2002-06-06)

12

My column is Character. So I should change the character to date ???

RUC :us: (BOB member since 2010-05-03)

13

You need to compare like to like. You can either compare character to character, or date to date, but not character to date. In order to remove the time from your character, I would first use the Oracle to_date() function to convert your current field to a proper date. Then you can move forward from there to compare that date to your other values, but they also need to be dates.

Dave Rathbun :us: (BOB member since 2002-06-06)

14

Convert your column to date.

If you’re doing a between, it won’t end well if you compare character strings of dates.