to format the output using CSTR in sql

[pol]

Hi all
I am giving the following sql to get month as string from a date

Select Cstr(month( salestarget.targetdate )) as monthno
from salestarget. So the output is shown as follows

1,10,11,12,2,3,4,5,6,7,8,9 like that. Please it is it possible to format the
string like
01,02,03,04,05,06,07,08,09,10 like that ?

Pol

 

[MikeB]

Hi all
I am giving the following sql to get month as string from a date

Select Cstr(month( salestarget.targetdate )) as monthno
from salestarget. So the output is shown as follows

1,10,11,12,2,3,4,5,6,7,8,9 like that. Please it is it possible to format the
string like
01,02,03,04,05,06,07,08,09,10 like that ?

Pol

Rather than convert it to string, could you simply format the number
as a 2-digit number? Also, the numbers should collate correctly
without the leading digit, whereas strings are collated differently.

 

[MikeB]

Hi all
I am giving the following sql to get month as string from a date

Select Cstr(month( salestarget.targetdate )) as monthno
from salestarget. So the output is shown as follows

1,10,11,12,2,3,4,5,6,7,8,9 like that. Please it is it possible to format the
string like
01,02,03,04,05,06,07,08,09,10 like that ?

Pol

To follow up, I did a quick search and I believe the following should
do it for you:

Select Format(month(salestarget.targetdate ), "00") as monthno from
salestarget.

 

[John Spencer]

Try
Format(TargetDate,"MM")

Select Format(salestarget.targetdate, "MM") as monthno
from salestarget

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[MikeB]

Try
Format(TargetDate,"MM")

Select Format(salestarget.targetdate, "MM") as monthno
from salestarget

l

Close, but no banana, eh?

 

[John W. Vinson]

Hi all
I am giving the following sql to get month as string from a date

Select Cstr(month( salestarget.targetdate )) as monthno
from salestarget. So the output is shown as follows

1,10,11,12,2,3,4,5,6,7,8,9 like that. Please it is it possible to format the
string like
01,02,03,04,05,06,07,08,09,10 like that ?

Don't use CStr() at all. Instead use

SELECT Format([salestarget].targetdate, "mm") as monthno from salestarget;

 

[Klatuu]

Your's will work. John's is more efficient.

l

Close, but no banana, eh?