asked on

SYSDATE default format

In the line

StrDate DATE DEFAULT SYSDATE;

I would like to have a DEFAULT SYSDATE format : 'DD-MON-YYYY HH24:MI:SS' ... only for this particular field .. the others have the format 'DD-MON-YYYY'

I would like to avoid to use ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'

Is there a way to do it ? Can we write something like StrDate DATE DEFAULT SYSDATE('DD-MON-YYYY HH24:MI:SS' )

Sorry for the question, i am pretty new in Oracle

Ora_Techie

declare
a varchar2(22) default to_Char(sysdate,'DD-MON-YYYY HH24:MI:SS' );

Ora_Techie

but i would ask "why you don't want to do alter session?"

anand_2000v

default sysdate will enter the complete date along with the time.
while retrieving the column you can specify the format using to_char
there is no requirement to enter the date in a specific format.

however if you are using oracle9i please use
StrDate timestamp DEFAULT SYSTIMESTAMP;

micmoo56

ASKER

Thank you but there is one more requirement the type of the field must remain a DATE, so the proposal

declare
a varchar2(22) default to_Char(sysdate,'DD-MON-YYYY HH24:MI:SS' );

is not returning the appropriate type (since it is a string)

I have tried strDate DATE DEFAULT TO_DATE(SYSDATE, 'DD-MM-YYYY HH24:MI:SS')

but for a reason the values appear as 06.03.1905

ASKER CERTIFIED SOLUTION

neo9414

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jrb1

You said:

"I would like to have a DEFAULT SYSDATE format"

How do you mean? What exactly are to you trying to accomplish? Neo is right. Dates are not formatted in the database. Are you trying to print them out on a report or something else?

jrb1

I think neo9414 solved this one.