Solved

Need equivalent to MS ACCESS "Format" function in SQL

Posted on 2015-01-12
9
196 Views
Last Modified: 2015-01-12
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?
0
Comment
Question by:dsoderstrom
  • 4
  • 3
  • 2
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40544666
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.
0
 
LVL 11

Expert Comment

by:Guru Ji
ID: 40544669
Try using

DATE_FORMAT('datetime_field','%m')
0
 
LVL 11

Expert Comment

by:Guru Ji
ID: 40544682
The best is try this which will return the month in two digit format

 SELECT CONVERT(char(2), GETDATE(), 101)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40544701
>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'
0
 

Author Comment

by:dsoderstrom
ID: 40544766
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.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40544774
>I need a single line of code that will return the value as a two position string regardless of the month.
Then my code will work (edited after I submitted it)

For example, the below returns the varchar '01'...
SELECT RIGHT('0' + CAST(MONTH(getdate()) as varchar(2)), 2)

Open in new window

0
 

Author Comment

by:dsoderstrom
ID: 40544873
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)
0
 

Author Comment

by:dsoderstrom
ID: 40544896
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.
0
 

Author Closing Comment

by:dsoderstrom
ID: 40544897
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.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question