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?
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?
Try using
DATE_FORMAT('datetime_fiel d','%m')
DATE_FORMAT('datetime_fiel
The best is try this which will return the month in two digit format
SELECT CONVERT(char(2), GETDATE(), 101)
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'
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'
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_GLBatchH eader.Acco untingPeri od) AS varchar(2)), 2)
RIGHT ('0' + CAST(MONTH(dbo.FS_GLBatchH
ASKER
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. Accounting Period AS varchar(2)), 2)
Thanks for your patience and for your help.
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.
Thanks for your patience and for your help.
ASKER
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. Accounting Period AS varchar(2)), 2)
Thanks for your patience and for your help.
so, when I changed your code to the following it worked.
RIGHT ('0' + CAST(dbo.FS_GLBatchHeader.
Thanks for your patience and for your help.
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.