I have a zoned decimal field as s 6

You have the field where? In a file? As a program variable?

and i am trying to convert it to date format as mmddyy in sql…

Is this a direct SQL query? Or is it embedded SQL? (If embedded, what is the programming language?)

Tom

It Should work if you are using Date function. What is the Error Message you are getting exactly??
Mention Programming language, that will be better for understanding.

Pradeep.

Its a field of a PF and i am running it through SQL query….

http://www.itjungle.com/mgo/mgo050102-story02.html

Shows how to convert zoned to SQL date

DATE(
SUBSTR(CHAR(19000000 + YMD),1,4) ||’-‘||
SUBSTR(CHAR(19000000 + YMD),5,2) ||’-‘||
SUBSTR(CHAR(19000000 + YMD),7,2)
)

If your SQL session format is *USA and the date separator is a forward slash (/), you could also convert the date this way:

DATE(
SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘||
SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘||
SUBSTR(CHAR(19000000 + YMD),1,4)
)

Here are a few examples lifted from this site
1 convert the date to char parse it into date format and use date function
or
2. use math to get the parsed date sections and then convert them to char
either way the DATE function processes a char string.
Phil

And don’t forget to make sure you have a valid date in the field to begin with.
If not, SQL (such as the sample code provided by Philpl1jb) will barf.
Gary

CHAR(19000000 + YMD)

Also be aware that this is looking for 6-digit numbers in the form YYMMDD. If your date values are stored as MMDDYY or DDMMYY, you’ll need to reformat them accordingly.

Also note that adding 19000000 to a 6-digit YYMMDD numeric date value will give you a year in the 20th century prior to 2000. You’ll need to decide if/when to use 20000000 or 19000000.

You might be best served by creating a UDF() that does the work.

Tom