Date Conversion
>
> I'm trying to move dates from a CHAR datatype to a DATE one. I keep
> either getting -1212 (Date conversion must contain a year, a month and a
> day), or the target field ending up null.
>
> I've checked all the obvious things like DBDATE (it's ymd2) and I've
> tried it with or without the date() function. Am I just missing something
> really dumb? For example, the following stored procedure generates a
> -1212 on the 'let p_date...' statement:
>
> create procedure date_test() returning date,char(12);>
> define p_date date;
> define p_string char(12);
>
> let p_string = "950101";
> let p_date = date(p_string);
>
> return p_date, p_string;
>
> end procedure;
>
>
> Any clues?
>
> akent@cix.compulink.co.uk (Andy Kent)
> ------------------------------------------------
> Freelance Informix Database Specialist,
> Redland, Bristol, England
>
Sounds like your DBDATE should be Y2MD.
While we're on this topic, I wrote a 'little' function which pulls a
date out of a field given a mask.
There are actually 2, one displays a date formatted according to the mask,
the other extracts. Enclosed.
-----------------------------------
################## Date format #####################################
# Display a date formatted according to a mask
####################################################################
FUNCTION form_date(dtm,mask)
DEFINE dtm DATETIME YEAR TO SECOND, mask, val CHAR(30), i,j SMALLINT
FOR i = 1 TO LENGTH(mask)-1
LET j = i + 1
IF mask[i,j] = "hh" THEN # hours
LET mask[i,j]= EXTEND(dtm, HOUR TO HOUR)
END IF
IF mask[i,j] = "mm" THEN # minutes
LET mask[i,j]= EXTEND(dtm, MINUTE TO MINUTE)
END IF
IF mask[i,j] = "ss" THEN # seconds
LET mask[i,j]= EXTEND(dtm, SECOND TO SECOND)
END IF
END FOR
# the rest Informix can understand
# on its own
LET val = TODAY using mask
RETURN val
END FUNCTION
################# extract date(time) ################################
# Given a string which contains a date/datetime and a mask which
# describes this string, extract the date/datetime, type is 1 for date,
# 2 for datetime
#####################################################################
FUNCTION ext_date(dtm_strg,mask, type)
DEFINE dtm_strg CHAR(30),
mask CHAR(30),
type, i,j SMALLINT,
date_ray ARRAY[6] OF SMALLINT,
mnth_name CHAR(3),
months ARRAY[12] OF CHAR(3),
retcode smallint,
dtm_var DATETIME YEAR TO SECOND, dt_var DATE,
year_on, month_on, day_on SMALLINT
FOR i = 1 TO 6 # init
LET date_ray[i]=0
END FOR
# set up month array
LET months[1]="JAN"
LET months[2]="FEB"
LET months[3]="MAR"
LET months[4]="APR"
LET months[5]="MAY"
LET months[6]="JUN"
LET months[7]="JUL"
LET months[8]="AUG"
LET months[9]="SEP"
LET months[10]="OCT"
LET months[11]="NOV"
LET months[12]="DEC"
LET year_on = 0
LET month_on = 0
LET day_on = 0
# roll through mask and figure out what is
# where, then extract it from the string.
FOR i = 1 TO LENGTH(mask)
CASE mask[i,i]
WHEN "Y" # YY or YYYY
# check length - make sure
# we don't go over
IF i < LENGTH(mask)-3 THEN
# ok to check for YYYY
LET j = i + 3
IF mask[i,j]="YYYY" THEN
# make sure is a number
let retcode = is_a_num(dtm_strg[i,j], 5)
IF retcode = 0 THEN
LET date_ray[1]=dtm_strg[i,j]
LET year_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 3
ELSE # must be YY
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[1]=dtm_strg[i,j]
LET year_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 1
END IF
ELSE # must be YY
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[1]=dtm_strg[i,j]
LET year_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 1
END IF
WHEN "M" # MM or MMM
IF i < LENGTH(mask)-2 THEN
LET j = i + 2
IF mask[i,j]="MMM" THEN
LET mnth_name = upshift(dtm_strg[i,j])
FOR j = 1 TO 12
IF mnth_name = months[j] THEN
LET date_ray[2]=j
LET month_on = 1
EXIT FOR
END IF
END FOR
LET i = i + 2
ELSE
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[2]=dtm_strg[i,j]
LET month_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 1
END IF
ELSE
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[2]=dtm_strg[i,j]
LET month_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 1
END IF
WHEN "D" # DD or DDD
IF i < LENGTH(mask)-2 THEN
LET j = i + 2
IF mask[i,j]="DDD" THEN # its a day name, ignore it
LET i = i + 2
ELSE
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[3]=dtm_strg[i,j]
LET day_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 1
END IF
ELSE
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[3]=dtm_strg[i,j]
LET day_on = 1
ELSE
RETURN "", -1
END IF
LET i = i + 1
END IF
WHEN "h" # hour
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN
LET date_ray[4]=dtm_strg[i,j]
ELSE
RETURN "", -1
END IF
LET i = i + 1
WHEN "m" # minute
LET j = i + 1
let retcode = is_a_num(dtm_strg[i,j], 3)
IF retcode = 0 THEN@@