Re: leading zeroes in day, month
Q: how to display DAY() and MONTH() of a date with leading zeros in DB-Access. The poster's attempt to substring an arithmetic expression ((DAY(date)+100)[2,3]) failed, and the suggested LPAD(month(d),2,'0') built-in wasn't available on his (apparently version 7) engine. Jonathan Leffler suggested a CASE expression or substringing a string form of the date. Doug Lawry supplied a working alternative: a small stored procedure (zerofill_2) that takes a CHAR(2) and prepends '0' while its length is under 2, called as zerofill_2(DAY(TODAY)) etc., tested on SE 7.23.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Mitja Udovc wrote: > I would like to show day(date) and month(date) with leading zeroes. Is it > any way to do that Lots - but which language are you using? DB-Access, ESQL/C, I4GL, something else? What I18N/L10N issues do you have to deal with - which sequence of fields do you use in a date string? You can probably use a CASE expression to fix it up. Alternatively, you could substring a string representation of the date value. Why does it matter? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
I'm using dbacess and i tried with substring but it doesn't work like SELECT (DAY(test.date)+100)[2,3] "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:4rtpb.4940$qh2.4352@newsread4.news.pas.earthlink.net... > Mitja Udovc wrote: > > I would like to show day(date) and month(date) with leading zeroes. Is it > > any way to do that > > Lots - but which language are you using? DB-Access, ESQL/C, I4GL, > something else? What I18N/L10N issues do you have to deal with - > which sequence of fields do you use in a date string? > > You can probably use a CASE expression to fix it up. Alternatively, > you could substring a string representation of the date value. > > Why does it matter? > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >
On Mon, 3 Nov 2003 15:32:04 +0100, "Mitja Udovc" <mitja.udovc@mf.uni-lj.si> wrote: >I'm using dbacess and i tried with substring but it doesn't work > >like > >SELECT (DAY(test.date)+100)[2,3] > > > What about using the lpad function, like.... select lpad(month(whatever_date), 2, "0"), lpad(day(whatever_date), 2, "0") from whatever_table Not too sure which version you have, but this works in 9.21 and 9.30. Possibly 7.31 too??? >"Jonathan Leffler" <jleffler@earthlink.net> wrote in message >news:4rtpb.4940$qh2.4352@newsread4.news.pas.earthlink.net... >> Mitja Udovc wrote: >> > I would like to show day(date) and month(date) with leading zeroes. Is >it >> > any way to do that >> >> Lots - but which language are you using? DB-Access, ESQL/C, I4GL, >> something else? What I18N/L10N issues do you have to deal with - >> which sequence of fields do you use in a date string? >> >> You can probably use a CASE expression to fix it up. Alternatively, >> you could substring a string representation of the date value. >> >> Why does it matter? >> >> -- >> Jonathan Leffler #include <disclaimer.h> >> Email: jleffler@earthlink.net, jleffler@us.ibm.com >> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >> >
lpad doesnt work Mitja "John Carlson" <john_carlson@whsmithusa.com> wrote in message news:4mrcqvg10v25e7qoguqnv5ouscnsem4old@4ax.com... > On Mon, 3 Nov 2003 15:32:04 +0100, "Mitja Udovc" > <mitja.udovc@mf.uni-lj.si> wrote: > > >I'm using dbacess and i tried with substring but it doesn't work > > > >like > > > >SELECT (DAY(test.date)+100)[2,3] > > > > > > > > > What about using the lpad function, like.... > select lpad(month(whatever_date), 2, "0"), lpad(day(whatever_date), 2, > "0") > from whatever_table > > > Not too sure which version you have, but this works in 9.21 and 9.30. > Possibly 7.31 too??? > > > >"Jonathan Leffler" <jleffler@earthlink.net> wrote in message > >news:4rtpb.4940$qh2.4352@newsread4.news.pas.earthlink.net... > >> Mitja Udovc wrote: > >> > I would like to show day(date) and month(date) with leading zeroes. Is > >it > >> > any way to do that > >> > >> Lots - but which language are you using? DB-Access, ESQL/C, I4GL, > >> something else? What I18N/L10N issues do you have to deal with - > >> which sequence of fields do you use in a date string? > >> > >> You can probably use a CASE expression to fix it up. Alternatively, > >> you could substring a string representation of the date value. > >> > >> Why does it matter? > >> > >> -- > >> Jonathan Leffler #include <disclaimer.h> > >> Email: jleffler@earthlink.net, jleffler@us.ibm.com > >> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ > >> > > >
On Mon, 3 Nov 2003 16:37:11 +0100, "Mitja Udovc" <mitja.udovc@mf.uni-lj.si> wrote: What version of the engine are you running?? >lpad doesnt work > >Mitja > >"John Carlson" <john_carlson@whsmithusa.com> wrote in message >news:4mrcqvg10v25e7qoguqnv5ouscnsem4old@4ax.com... >> On Mon, 3 Nov 2003 15:32:04 +0100, "Mitja Udovc" >> <mitja.udovc@mf.uni-lj.si> wrote: >> >> >I'm using dbacess and i tried with substring but it doesn't work >> > >> >like >> > >> >SELECT (DAY(test.date)+100)[2,3] >> > >> > >> > >> >> >> What about using the lpad function, like.... >> select lpad(month(whatever_date), 2, "0"), lpad(day(whatever_date), 2, >> "0") >> from whatever_table >> >> >> Not too sure which version you have, but this works in 9.21 and 9.30. >> Possibly 7.31 too??? >> >> >> >"Jonathan Leffler" <jleffler@earthlink.net> wrote in message >> >news:4rtpb.4940$qh2.4352@newsread4.news.pas.earthlink.net... >> >> Mitja Udovc wrote: >> >> > I would like to show day(date) and month(date) with leading zeroes. >Is >> >it >> >> > any way to do that >> >> >> >> Lots - but which language are you using? DB-Access, ESQL/C, I4GL, >> >> something else? What I18N/L10N issues do you have to deal with - >> >> which sequence of fields do you use in a date string? >> >> >> >> You can probably use a CASE expression to fix it up. Alternatively, >> >> you could substring a string representation of the date value. >> >> >> >> Why does it matter? >> >> >> >> -- >> >> Jonathan Leffler #include <disclaimer.h> >> >> Email: jleffler@earthlink.net, jleffler@us.ibm.com >> >> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >> >> >> > >> >
You must be on version 7 if you don't have "lpad". Here is a workaround
tested on SE 7.23 (the oldest version I have available):
CREATE PROCEDURE zerofill_2(number CHAR(2)) RETURNING CHAR(2);
WHILE LENGTH(number) < 2
LET number = "0" || number;
END WHILE
RETURN number;
END PROCEDURE;
-- Test --
SELECT
zerofill_2(DAY(TODAY)),
zerofill_2(MONTH(TODAY)),
zerofill_2(YEAR(TODAY) - 2000)
FROM
systables
WHERE
tabname = "systables";
Regards,
Doug Lawry
www.douglawry.webhop.org