SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DATEADD resulting in dd month yyyy format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwirky
Starting Member

Australia
8 Posts

Posted - 02/17/2013 :  19:34:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/17/2013 :  20:18:21  Show Profile  Reply with Quote
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!

Edited by - James K on 02/17/2013 20:38:55
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/17/2013 :  20:26:36  Show Profile  Reply with Quote
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

Australia
8 Posts

Posted - 02/17/2013 :  23:28:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/18/2013 :  07:46:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000