How do you show the month name in a date formula? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How do you show the month name in a date formula?

Author

Topic

BadBoy House
Starting Member

41 Posts

Posted - 2010-06-18 : 04:16:52

The following formula works out a due date from our database:

DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09")) AS "Payment Due Date",

However it displays dates in the format of 01-Aug-2010.

I need the date to display in the format of 1 August 2010 (no leading zero for single digits and the full month name).

Can anyone tell me how to do this?

BadBoy House
Starting Member

41 Posts

Posted - 2010-06-18 : 05:05:30

I've managed to display the dates as separate fields (based on the date of the date field in the database) but now I need to bring them all together to display as one line. Any ideas?

This is my formula so far:

DAY (DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) AS 'Day',
DATENAME(month, DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) AS 'Month',
YEAR (DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) AS 'Year'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 05:18:50

If you want to format in the report, do formation there

Otherwise, use one of the following

1
DAY (DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) +' '+
DATENAME(month, DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) +' '+
YEAR (DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09")))

2
convert(varchar(12),tblClientExtraDetails."A/C_Yr_End_09",13)

Madhivanan

Failing to plan is Planning to fail

BadBoy House
Starting Member

41 Posts

Posted - 2010-06-18 : 05:35:23

i've actually done it by inserting the day, datename and year fields separately into the word merge i'm creating.

However the code you mentioned in option 1 generates an error:

"Conversion failed when converting the nvarchar value 'august' to data type int.

Option 2 you mention displays the month as Aug - I need August.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 07:09:24

cast(DAY (DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) as varchar(2))+' '+
DATENAME(month, DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) +' '+
cast(YEAR (DATEADD(DAY, 1, DATEADD(MONTH, 9, tblClientExtraDetails."A/C_Yr_End_09"))) as varchar(4))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources