Solved

Need equivalent to MS ACCESS "Format" function in SQL

Posted on 2015-01-12
9
188 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
 
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now