How to convert the datetime value to nvarchar and want to format it "Month, Year" e-g October 1st 2009 value should get converted to "October, 2009"
A:
One way would be to use datename to extract the pieces you needed in name format, so:
select Convert(nvarchar,datename(m,getdate())) + N', ' + Convert(nvarchar,datename (yy,getdate()))
And replace getdate() with your date variable / field.
Andrew
2009-10-01 12:35:40
would be more efficient to convert everything at one time to nvarchar. also don't forget, if you don't specify a nvarchar length in the CONVERT, it defaults to 30 and will truncate. for example run this _SELECT CONVERT(nvarchar,'abcdefghijklmnopqrstuvwxyz1234567890')_ you only get the first 30 characters and will not see the truncated characters: "567890", and there is no error or warning.
KM
2009-10-01 12:58:18
A:
The DateName function will provide the formatting you require:
DATENAME(m, date) + ', ' + DATENAME(yyyy, date)
Converting to nvarchar of a specific size can be done through the cast function:
CAST(value AS nvarchar[30])
Mike
2009-10-01 12:36:11
Datepart is the numeric representation, not the name representation unfortunately.
Andrew
2009-10-01 12:37:38
you need DATENAME() for the year too, like my answer, otherwise: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'October, ' to data type int.
KM
2009-10-01 12:47:23
+1
A:
Try this
DECLARE @DateTime DATETIME
SET @DateTime = '01 Oct 2009'
SELECT @DateTime
SELECT DATENAME(mm, @DateTime) + ', ' + CAST(DATEPART(yy, @DateTime) AS VARCHAR(4))
astander
2009-10-01 12:36:16
+2
A:
use this:
select CONVERT(nvarchar(50), DATENAME(m, getdate())
+ ', '
+ DATENAME(yyyy, getdate())
)
OUTPUT:
--------------------------------------------------
October, 2009
(1 row(s) affected)
KM
2009-10-01 12:46:37
If he wants to put it into a variable already declared as nvarchar, the convert is not needed as it will implicitly convert at the time the information is placed in the variable. The only place I know of where varchar data doesn't implicitly convert is in SSIS.
HLGEM
2009-10-01 14:29:27
A:
Try the following query:
Select case Convert(int, day(getdate())) when 1 then '1st' when 2 then '2nd'
when 3 then '3rd' else Convert(varchar, day(getdate()))+'th' end +' '+ Convert(varchar, Datename(m,getdate()))+' ' +Convert(varchar, Datename(yy,getdate())) as Date
You can replace getdate() with any other date.
Please check if it helps.
Himadri
2009-10-01 13:08:52