| Author |
 Topic  |
|
|
kwirky
Starting Member
Australia
8 Posts |
 Posted - 02/17/2013 : 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
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/17/2013 : 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! |
Edited by - James K on 02/17/2013 20:38:55 |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/17/2013 : 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 |
|
|
|
kwirky
Starting Member
Australia
8 Posts |
Posted - 02/17/2013 : 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 :) |
|
|
|
James K
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/18/2013 : 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)) |
|
|
| |
Topic |
|
|
|
DATEADD resulting in dd month yyyy format
