to format the output using CSTR in sql

P

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
 
M

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
Click to expand...

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.
 
M

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
Click to expand...

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.
 
J

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
 
J

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 ?
Click to expand...

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL statment to convert integer to string 4
Subquery Correllated In Select Clause - Is this possible? 3
To give sql by removing empty space from the data 3
How to select all months from a table to include in a report 3
to give if condition in MS Access 2
to make datatype as decimal or double for temprary column 4
Data Type Mismatch even with CStr? 3
Desparate to solve Type Mismatch problem 3

Top