?
Solved

Need equivalent to MS ACCESS "Format" function in SQL

Posted on 2015-01-12
9
Medium Priority
?
220 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
[X]
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
  • 4
  • 3
  • 2
9 Comments
 
LVL 66

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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