Link to home
Start Free TrialLog in
Avatar of dsoderstrom
dsoderstrom

asked on

Need equivalent to MS ACCESS "Format" function in SQL

I have a field in an SQL table containing the number of the month.
I want to format this data in a query to a two position text field.
When I use the STR function it returns the value for the month of January as '1'.  I want to return the value as a two character field with a leading zero ('01').  In MS Access I would use FORMAT(monthfield,"00"). Is there a function for doing this in SQL?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

In SQL Server ou gotta 'pad' like this...
SELECT RIGHT('0' + CAST(MONTH(month_number_goes_here) as varchar(2)), 2) 

Open in new window


Keep in mind this returns a varchar (aka string).  SQL Server like Access does not support leading zeros in numeric data types.
Try using

DATE_FORMAT('datetime_field','%m')
The best is try this which will return the month in two digit format

 SELECT CONVERT(char(2), GETDATE(), 101)
>I want to format this data in a query to a two position text field.
Give us a mockup of what you are expecting here as a return value, i.e. just the month '04' as a string, or a date with a two digit month '2014-04-05 12:34:56 PM'
Avatar of dsoderstrom
dsoderstrom

ASKER

Sorry, I should have a been a little more precise in my question.
I want to return the date as a two position string value.  For months 10, 11 and 12 the STR function works perfectly.  But for months 1 through 9 I need to add a leading zero.  I need a single line of code that will return the value as a two position string regardless of the month.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Okay, I entered the following line of code.  It returns '01' regardless of the month.  What did I do wrong?

RIGHT ('0' + CAST(MONTH(dbo.FS_GLBatchHeader.AccountingPeriod) AS varchar(2)), 2)
I've requested that this question be closed as follows:

Accepted answer: 0 points for dsoderstrom's comment #a40544873

for the following reason:

You code worked.
Actually field that I am formatting is not a date field.  It is a already a field containing only the month.
so, when I changed your code to the following it worked.

RIGHT ('0' + CAST(dbo.FS_GLBatchHeader.AccountingPeriod AS varchar(2)), 2)

Thanks for your patience and for your help.
Actually field that I am formatting is not a date field.  It is a already a field containing only the month.
 so, when I changed your code to the following it worked.

 RIGHT ('0' + CAST(dbo.FS_GLBatchHeader.AccountingPeriod AS varchar(2)), 2)

 Thanks for your patience and for your help.