InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 17:21:55 GMT20http://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxThere's always alternate ways of formatting a date. Whether you'd want to use them is another story.[code="sql"]DECLARE @product_name VARCHAR(20) = 'Vehicles' ,@expiry_date DATETIME = '2012-09-10'SELECT STUFF(RIGHT(CONVERT(VARCHAR(12), @expiry_date,107), 9), 1, 0 ,'Product : ' + @product_name + ' (Expires ' + DATENAME(MM, @expiry_date)) + ')'[/code]Wed, 19 Dec 2012 18:11:24 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxOr this:[code="sql"]DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003', @Prod VARCHAR(MAX), @Product_Name VARCHAR(10) = 'Vehicles'SET @Prod= 'Product: ' + @Product_name + ' (Expires ' + STUFF(CONVERT(VARCHAR(12), @Expiry_Date, 107), 1, 3, DATENAME(MM, @Expiry_Date)) + ')'SELECT @Prod[/code]Wed, 19 Dec 2012 09:12:11 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxHow about this:[code="sql"]DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003';SELECT DATENAME(MONTH, @Expiry_Date) + ' ' + DATENAME(DAY, @Expiry_Date) + ', ' + DATENAME(YEAR, @Expiry_Date);[/code]Wed, 19 Dec 2012 08:50:01 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspx[code="sql"]DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003', @Prod VARCHAR(MAX), @Product_Name VARCHAR(10) = 'Vehicles'SET @Prod=@Product_name + RTRIM(@Product_name)+ ' (Expires '+RTRIM(DATENAME(MM, @Expiry_Date ) + RIGHT(CONVERT(VARCHAR(12), @Expiry_Date ,107),9))+ ')'SELECT @Prod[/code]Output isVehiclesVehicles (Expires December 25, 2012)Wed, 19 Dec 2012 08:43:34 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxi want to append the expiry date to a variable like below..DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003'SET @Prod=@Product_name + RTRIM(@Product_name) +SELECT '(Expires '+RTRIM(DATENAME(MM, @Expiry_Date ) + RIGHT(CONVERT(VARCHAR(12), @Expiry_Date ,107),9))+ ')'but its not working...Wed, 19 Dec 2012 08:40:27 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxDECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003'SELECT '(Expires '+RTRIM(DATENAME(MM, @Expiry_Date ) + RIGHT(CONVERT(VARCHAR(12), @Expiry_Date ,107),9))+ ')'Gives me an output of (Expires December 25, 2012)Wed, 19 Dec 2012 08:29:54 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxExpiry_date Format is : DATETIMEWed, 19 Dec 2012 08:27:38 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxWell something is wrong as I run it I get the , in the output between the date and year which is what the convert 107 does.What is the data type of the parameter @expiry_dateWed, 19 Dec 2012 08:24:31 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspx[b]First Query[/b]SET @Prod=@Product_name + RTRIM(@Product_name) + ' (Expires ' +RTRIM(DATENAME(MM, @expriry_date ) + RIGHT(CONVERT(VARCHAR(12), @expriry_date ,107),9))+ ')' +CHAR(10) Result is : Product : Vehicles (Expires September 30 2012)[b]Second Query[/b]SELECT '(Expires '+RTRIM(DATENAME(MM, GETDATE()) +','+ RIGHT(CONVERT(VARCHAR(12), GETDATE() ,107),9))+ ')'Result is : Product : Vehicles (Expires September, 30 2012)--I want comma after September 30.Eg: September 30, 2012Wed, 19 Dec 2012 08:21:29 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxthen do what you where doing in the first place, this is how it outputsSELECT '(Expires '+RTRIM(DATENAME(MM, GETDATE() ) + RIGHT(CONVERT(VARCHAR(12), GETDATE() ,107),9))+ ')'(Expires December 19, 2012)Wed, 19 Dec 2012 08:17:11 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxHi Anthony,Small change in my requirement, I want only one comma after the Date.Product : Vehicles (Expires September 30, 2012)Wed, 19 Dec 2012 08:15:12 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxYou want to modify the output to one which doesnt happen when you convert a date so you have to manually tell it the format which is why you need to manually put in the extra comma using ','Wed, 19 Dec 2012 02:34:27 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxThank U anthony,Its working...Is there any alternate syntax/query to get the above result.i mean without using above code i want to generate the same output...Wed, 19 Dec 2012 02:32:27 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxSELECT '(Expires '+RTRIM(DATENAME(MM, GETDATE()) +','+ RIGHT(CONVERT(VARCHAR(12), GETDATE() ,107),9))+ ')'Gives (Expires December, 19, 2012)Wed, 19 Dec 2012 02:06:56 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398230-391-1.aspxHi Team,using below query, am assigning a Product_name and its expriry_date to a variable Prod[b] SET @Prod=@Product_name + RTRIM(@Product_name) + ' (Expires ' +RTRIM(DATENAME(MM, @expriry_date ) + RIGHT(CONVERT(VARCHAR(12), @expriry_date ,107),9))+ ')' +CHAR(10) [/b]but am getting out put as below[b]Product : Vehicles (Expires September 30 2012)[/b]i want out put like below (comma after month)[b]Product : Vehicles (Expires September 30, 2012)[/b]Please helpWed, 19 Dec 2012 01:37:10 GMTMinnu