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
 General SQL Server Forums
 New to SQL Server Programming
 DATEADD resulting in dd month yyyy format

Author  Topic 

kwirky
Starting Member

8 Posts

Posted - 2013-02-17 : 19:34:03
Hi,
I am trying to get a result which shows 7 days from now in the format of dd month yyyy.

I started with the below however the result is 25 Feb 2013. I need this to show 25 February 2013.

SELECT CONVERT(VARCHAR(20), DATEADD(dd,7,GETDATE()),106)

I then tried the below and got Feb 25 2013 11:32AM February 2013 which is kind of on the right track.

select CONVERT(nvarchar,(DATEADD(dd,7,GETDATE())))
+ ' ' + CONVERT(nvarchar,(DATENAME(MONTH,GETDATE())))
+ ' ' + CONVERT(nvarchar,(DATEPART(year,GETDATE())))

Can anyone help with this?

Thank you

Thanks
Kwirky :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-17 : 20:18:21
If you are using SQL 2012, use one of these:
SELECT 
FORMAT(DATEADD(dd,7,GETDATE()),'dd MMMM yyyy'),
FORMAT(DATEADD(dd,7,GETDATE()),'D','en-gb') ;
If you are on an earlier version of SQL Server, there are a few different ways you could do this, none that I particularly like. Here is one for example.
SELECT STUFF(CONVERT(VARCHAR(32),DATEADD(dd,7,GETDATE()),103),3,4,' ' 
+ DATENAME(weekday,DATEADD(dd,7,GETDATE()))+ ' ')
What you posted would work as well, just needs some minor tweaking:
select CONVERT(nvarchar(2),DAY(DATEADD(dd,7,GETDATE())))
+ ' ' + CONVERT(nvarchar(20),(DATENAME(MONTH,DATEADD(dd,7,GETDATE()))))
+ ' ' + CONVERT(nvarchar(4),(DATEPART(year,DATEADD(dd,7,GETDATE()))))
Usually it is better to do this type of formatting at the client side (where you consume data), for example, in an SSRS report.

Editing: Revising my posting (in red) after seeing Jim's comment below. Thanks Jim!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-17 : 20:26:36
This will give you what you want based on your example. Formatting really should be done in the front end, though.

select
CONVERT(varchar(2), DATEPART(dd,getdate()+7))
+ ' ' + CONVERT(nvarchar(20),(DATENAME(MONTH,GETDATE())))
+ ' ' + CONVERT(nvarchar(4),YEAR(getdate()))


You should always include a length with any string data type, i.e,
nvarchar(10) , not just nvarchar

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

kwirky
Starting Member

8 Posts

Posted - 2013-02-17 : 23:28:05
Thank you JimF. The script you provided worked perfectly.

select
CONVERT(varchar(2), DATEPART(dd,getdate()+7))
+ ' ' + CONVERT(nvarchar(20),(DATENAME(MONTH,GETDATE())))
+ ' ' + CONVERT(nvarchar(4),YEAR(getdate()))



Thanks
Kwirky :)
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 07:46:42
Please make the change I have indicated in red below to Jim's query. Otherwise, you will get the wrong results during the last week of each month and during the last week of each year:
CONVERT(varchar(2), DATEPART(dd,getdate()+7))
+ ' ' + CONVERT(nvarchar(20),(DATENAME(MONTH,GETDATE()+7)))
+ ' ' + CONVERT(nvarchar(4),YEAR(getdate()+7))
Go to Top of Page
   

- Advertisement -