How to make extract month or day a two digit number. 1

[thewhistler1]

I'm trying to produce an order number out of parts of the date.

I tried this:
select extract(month from now());
and I get: 2

then I did this:
select extract('month' from now())|| extract('day' from now());
and I get: 210

But what I would like to know is how to make it give me a two digit number for the month and for the day such as 0211.

 

[feherke]

Hi

If you are concatenating them, then why you want numeric values anyway ?

[blue]psql=#[/blue] [b]select[/b] now(),to_char(now(),[i]'mmdd'[/i]);
              now              | to_char 
-------------------------------+---------
 2008-02-11 21:27:46.976251+02 | 0211
(1 row)

Feherke.

http://rootshell.be/~feherke/

 

[thewhistler1]

Thanks feherke

Why do I want it numeric you ask. Because as I said I want to create an order number consisting of the date and a number counting up such as :

0211/0001 = order 1 for date 2/11
0211/0002 = order 2 for date 2/11
0211/0003 = order 3 for date 2/11

But your suggestion did work so thanks.

 

[stefanwagner]

If ever possible, use a pure serial object for ordernumber and the like.
Mixing semantic values into serials often bites you in the long run.
Consider a date of an order has to be changed - now you have to change your serial, to keep it consistent?
Reorder hundert of orders? This will be a pain!

Serials should never contain semantic meaning of any kind - you shouldn't even rely on the serials being in some order.

They shall identify a record and that's it.

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[thewhistler1]

stefanwagner

It's not for a serial number, it is for an order number for a POS system. Nothing has to be renumbered, if the order gets cancelled it is still in the system but with a cancelled status. If the date gets changed for the order when there are other orders that were entered after it then that order number will just be missing from that days sales. But all tables that have this number also have a serial number.

Thanks