Re: AW: Embedded SQL formatting dates

[Booth Martin <booth@xxxxxxxxxxxx>]

I have said the question badly.  I understand that a date field knows 
what it is; I do not to tell it.  But it does need to know what is being 
fed into it.  That is the place I am at with this issue.

I have a field in a data file which is 7/0 and is cyymmdd.   I want to 
use it as a date field so I can get the week.  Presently that is being 
done by substringing the digits and replacing the leading "1" with a 
"20".  That seems cumbersome to me but maybe thats the only way to do it?

On 1/1/2016 10:58 AM, Birgitta Hauser wrote:
> SQL does not care about date formats. A date is only a numeric value and
> date formats are only used to make this scaliger no readable.
> If you debug your code and fetch any date value into a date field. The
> format of the date that is displayed depends on the date format of the RPG
> variable.
> If you want to convert or display the date into any other format, it has to
> be converted into a character version, by  using one of the scalar functions
> CHAR() or VARCHAR_FORMAT().
>
> Example:
> Exec SQL Values(Char(Current_Date, USA),
>                             VarChar_Format(Current_Date, 'MM/DD/YY'),
>                             VarChar_Format(Current_Date, 'MMDDYYYY'))
>              into :HostDateMMDDYYYY, :HostDateMMDDYY;
>
> If you need to convert a date into a numeric representation in the format
> YYYYMMDD, this can be done since release 7.2 with one of the scalar
> functions DEC(), Integer() or BigInt():
>
> Example:
> Exec SQL Values(Dec(Current_Date, 8, 0),
>                            Int(Current_Date),
>                            BigInt(Current_Date))
>             Into :HostDateDec, :HostDateInt, :HostDateBigInt;
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Booth
> Martin
> Gesendet: Thursday, 31.12 2015 20:48
> An: RPG programming on the IBM i / System i
> Betreff: Embedded SQL formatting dates
>
> Birgitta tantalized me  a bit ago.  SQL does so much so well.
>
> So far I have not found a way to set the output format for a date.  This
> does not work:
>
>              exec sql select date(:WRKDAT)
>                into :MDYYDATE "mm-dd-yyyy" from SYSIBM/SYSDUMMY1;
>
> (As an aside, it seems to need SYSIBM/SYSDUMMY1.  At least I am #1, eh?
> Or does that mean something else?)
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> http://archive.midrange.com/rpg400-l.