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 youThanksKwirky :) |
|
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! |
|
|
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 nvarcharJimEveryday I learn something that somebody else already knew |
|
|
kwirky
Starting Member
8 Posts |
Posted - 2013-02-17 : 23:28:05
|
Thank you JimF. The script you provided worked perfectly.selectCONVERT(varchar(2), DATEPART(dd,getdate()+7))+ ' ' + CONVERT(nvarchar(20),(DATENAME(MONTH,GETDATE())))+ ' ' + CONVERT(nvarchar(4),YEAR(getdate()))ThanksKwirky :) |
|
|
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)) |
|
|
|
|
|