Skip to content

Database Ops

July 23, 2008

File – SQL and CSV.

========================

set colsep,
set echo off
set pagesize 0
set lines 1000
set feedback off
spool C:\Temp\UserAuthReport.csv

Terrible problem with sysdate –

The thing is that you do NOT need to format Sysdate while inserting in oracle database. Unlink MySQL oracle does not have two different datatypes (timestamp,date) but has only one datatype which is Date.  So when you insert date and you want that data to be formatted – need to format while insertion – simply insert sysdate and while retreiving you can do to_char( sysdate, ‘dateFormat’)

to_date converts date to char and to_char converts char to date – so need not do like this

to_date(sysdate, dateFormat) since sysdate is already a date!.

and do not rely on database varchar2 to date conversion since it is not reliable so do not do like this

insert into login_date values to_char(sysdate, dateFormat);
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: