Passing a string as a Date !

[Dhafir]

Hi,
I am new to DB2 and appreciate any help.

Problem: I need to pass a date/time string with certain format to a Date/Time DB2 field. I am looking to an equivallent MS's SQL CONVERT(...) or Oracle's TO_DATE(...) function in DB2, but can't find one!

I tried DATE( '23.11.2002', EUR) but it didn't work!

Honestly, DB2 documentation is awful! I can't find a decent reference to DB2 functions like DATE and CHAR, I even bought Osborn SQL complete reference book and couldn't find those in it !

Thanks for the help.

Dhafir

 

[blom0344]

Hello Dhafir,

The Date function has the date-part extracted from date or timestamp.
DATE("2002-09-25&quot will give you the conversion you want.

May I point you to the following (great) link:

http://ourworld.compuserve.com/homepages/Graeme_Birchall/

for DB2 cookbooks T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Dhafir]

Thanks T.Blob,

The Date(...) function is not helpful because I don't have control over the string format !

Let me re-phrase my question:
I have a date string like this:
"Thursday 26, Sept 2002"

The format of this date is "DAY dd, MON yyyy" ..
How can I convert this string to a valid Date data type ?

Thanks for the link

Dhafir

 

[MarcLodge]

Dhafir,
From the date string that you have ("Thursday 26, Sept 2002&quot I don't think that you are going to be able to use DB2 or SQL in order to convert it into a DB2 date.

Whatever language you are working in, you will need to utilise in order to write a routine to transform this type of date into DB2. I think. Unless somebody knows different....

 

[Dhafir]

Thanks Marc,

It looks like this is the case ! The free format is available with Oracle the way I want it to be, and even SQL Server has a good range of formats. DB2 is very limited in this area. It accepts date string in either of the following format :
dd.mm.yyyy
dd-mm-yyyy

very limited!

 

[blom0344]

Hello Dhafir,

First thing that comes to mind is to throw the issue with converting your format to a standard format (DB2) to one of the VBA forums to think up a routine to get it done prior to feeding it to DB2. Something that cuts out the DAY part, splits and translates the rest to 3 parts and then reassembles. You're right about the state of DB2 documentation. It is somewhere out there, but the search is long and hard ......................... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[MarcLodge]

Dhafir,
Without a shadow of a doubt, it can be done fairly easily using a progamming language. It depends upon the environment that you are running in as to which language will be best for your purposes.

Good luck, and let us know if you need anymore help.
Marc

 

[Dhafir]

Thanks to everyone, Blom and Marc for taking the time in help, I really apprecaite that.

It will be easier for me to change the date format to something DB2 understands and at the same time will be compatible with SQL Server and Oracle, my code actually could talk to all of them.

regards


Dhafir

 

[MarcLodge]

Dhafir,
Before giving up on it, I think it may be possible!! But (and this is a big one) your date format would need to be strict e.g. The DAY and MON parts of your 'DAY dd, MON yyyy' would always have to be the same length. For sake of argument, let's say that the DAY is always 9 chars and the MON is always 4. If this was the case today's date would be:
Saturday 28, Sept 2002

Taking this rather unlikely scenario into account and assuming that we can find the date in a field called DATEFIELD, I reckon that the following or something similar would work:

SELECT DATE(SUBSTR(DATEFIELD,11,2)||-||
CASE SUBSTR(DATEFIELD,15,4)
WHEN 'Jan ' THEN '01'
WHEN 'Feb ' THEN '02'
etc
WHEN 'Dec ' THEN '12'
END||-||
SUBSTR(DATEFIELD,20,4))

I haven't got a DB2 system here with me at home so I can't check it, but it seems to me like it's some sort of potential. Maybe.

Marc

 

[Dhafir]

Thanks Marc,
certainly would work for fixed DAY and MON length, good thinking. However, changing the date format is more feasible.

Many thanks again.

Dhafir

 

[sathyarams]

Rather than handling the dat conversion external to DB2, you can write a DB2 User Defined Function in Java/C . You can call this function just as you do a TO_DATE function in Oracle

HTH

Cheers

Sathyaram