| View previous topic :: View next topic |
| Author |
Message |
pzmohanty
Beginner
Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
|
 Posted: Wed Sep 08, 2004 10:20 am Post subject: db2 date formats |
 |
|
hi all,
I have following 2 queries:
1)how can i convert a date into multiple formats like ( yyyy/mm/dd or mm/dd/yyyy etc).
2) how can i convert a string containing date in various formats to date.
thanx in advn,
P.R.mohanty |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
pzmohanty
Beginner
Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
|
| Posted: Wed Sep 08, 2004 10:47 am Post subject: |
|
|
hi kolusu,
your reply do solves my first problem.
but yet to find solution for second query.
by using CHAR function we can convert DATE datatype to different format date strings.
but how to convert different format date given as strings to date data type.
for example how to convert 'CCYY/MM/DD' date string to DATE data type.
Thanx in advn.
P.R.Mohanty |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Wed Sep 08, 2004 10:53 am Post subject: |
|
|
pzmohanty,
| Quote: |
but how to convert different format date given as strings to date data type.
for example how to convert 'CCYY/MM/DD' date string to DATE data type.
|
A column defined as DATE is nothing but a column of 10 characters. Internally when you are inserting/loading records into the table, DB2 checks if the 10 characters is a valid date or not. So if you have a 10 character field,and it is a valid date then you don't need to do any conversion.
Hope this helps...
Cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
pzmohanty
Beginner
Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
|
| Posted: Sun Sep 12, 2004 4:08 am Post subject: |
|
|
hi kolusu,
| Quote: |
Internally when you are inserting/loading records into the table, DB2 checks if the 10 characters is a valid date or not.
|
what exactly do you mean by a valid date??
does valid date means date in any format ( DD/MM/CCYY or MM/DD/CCYY or CCYY/MM/DD).
if that is true then what is wrong with this query----
SELECT DATE('2004/12/12') FROM SYSIBM.SYSDUMMY1
the error being thrown by SPUFI is following :
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID
DSNT418I SQLSTATE = 22007 SQLSTATE RETURN CODE
Thanx in advn
P.R.Mohanty
_________________
Priya Ranjan Mohanty
Consultant
Kanbay Software (I) pvt. Ltd.
Hyderabad |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Sun Sep 12, 2004 7:42 pm Post subject: |
|
|
| Quote: |
what exactly do you mean by a valid date??
does valid date means date in any format ( DD/MM/CCYY or MM/DD/CCYY or CCYY/MM/DD).
|
Pzmohanty,
When your DB2 admin installed DB2 at your shop, there is an application programmign defaults panel screen where he specifies values to be used as default values by the program preparation panels, the program preparation CLIST (DSNH), and the precompiler. They can also be used as defaults by other programs, such as Query Management Facility (QMF) and spufi also.
The date format can be ISO(yyyy-mm-dd), JIS(yyyy-mm-dd), USA(mm/dd/yyyy) , EUR( dd.mm.yyyy), LOCAL(your choice)
Depending on the default date format , DB2 validates the dates when inserting/updating a date field.
Now if your shop has defined USA format as the default date format , then your sql query will give you an error sql code of -180 which was what you got.
check this link for Application programming defaults panel 2: DSNTIP4
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNIGH13/2.4.19?DT=20030515110323
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
