date function doesn't work anymore...

[anneoctaaf]

I'm using the date function to convert a string to a date:

dt_today = today()
li_maand = month(dt_today )
li_jaar = year(dt_today )
li_dag = day(dt_today )

IF li_month > 1 THEN
li_month = li_month - 1
ELSEIF li_month = 1 THEN
li_month = 12
li_year = li_year - 1
END IF

ls_date = string(li_day) + '/' + string(li_month) + '/' + string(li_year)
dt_last_month = date(ls_date)

This used to work, but since a day or two it doesn't. Th edebugger says dt_last_month = 1-1-1900 (while ls_date is correct)

Does anybody know what's going on?

 

[xsyguy]

When you rebuild the date using the month, day, and year, you are not adjusting the day for the last day of the new month. Given your code example, if Today() is March 30, 2004 ls_date will be set to '30/2/2004' (February 30, 2004), which is an invalid date.

 

[anneoctaaf]

Thanx!

 

[pgaec]

y dont u use dateadd sql fn instead

datetime ldt_return
dt_today = today()
li_month = month(dt_today )

if li_month > 1 THEN
SELECT dateadd(mm,-1,dt_today)
INTO :ldt_return
WHERE datepart(mm,dt_today) > 1
USING sqlca;
else
SELECT dateadd(yy,-1,dt_today)
INTO :ldt_return
WHERE datepart(mm,dt_today) = 1
USING sqlca;
end if

 

[anneoctaaf]

I get a syntax error, but i can't find out what it is...

 

[mbalent]

If the object of the process is to get a date that was a month in the past you could use the Relativedate method. [ie.,Relativedate(Today(),-30)]

 

[pgaec]

@mbalent, A month can have 31,28 and 29 days too

 

[mbalent]

My example goes a set 30 days into the past from the current date. Whether you use this technique or not depends on what the business requirement is.

 

[anneoctaaf]

Yeah, i know, that's why i like the sql function datepart, but somehow i get a syntax error and i looked and looked, but it seems fine to me. It's a shame PB doesn't have a relativedate with months, any way i just have to write my own function...

Thanx anyway for your replies!

 

[pgaec]

oops my code should have been

y dont u use dateadd sql fn instead

datetime ldt_return
dt_today = today()
li_month = month(dt_today )

if li_month > 1 THEN
SELECT dateadd(mm,-1,:dt_today)
INTO :ldt_return
WHERE datepart(mm,:dt_today) > 1
USING sqlca;
else
SELECT dateadd(yy,-1,:dt_today)
INTO :ldt_return
WHERE datepart(mm,:dt_today) = 1
USING sqlca;
end if

 

[anneoctaaf]

thanx Mexicomeat, i'm going to try again...

 

[xsyguy]

It appears to me that the above code will subtract a year from the month if the month is January. (ie: If the date is 01/30/2004 it will return 01/30/2003 instead of 12/30/2003) I don't think the test for the month is necessary. The following code should be sufficient.

Date dt_today
DateTime ldt_return
dt_today = Today()

SELECT dateadd(mm,-1,:dt_today)
INTO :ldt_return
USING sqlca;