Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need equivalent to MS ACCESS "Format" function in SQL

Posted on 2015-01-12
9
192 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 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