Date format

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Date format

15 posts, Page 1 of 2

1 2 »»

Date format

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Wednesday, December 19, 2012 1:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

Hi Team,

using below query, am assigning a Product_name and its expriry_date to a variable Prod

SET @Prod=@Product_name + RTRIM(@Product_name) + ' (Expires '
+RTRIM(DATENAME(MM, @expriry_date ) + RIGHT(CONVERT(VARCHAR(12), @expriry_date ,107),9))+ ')' +CHAR(10)

but am getting out put as below
Product : Vehicles (Expires September 30 2012)

i want out put like below (comma after month)

Product : Vehicles (Expires September 30, 2012)

Please help

Post #1398230

anthony.green

Posted Wednesday, December 19, 2012 2:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

SELECT '(Expires '+RTRIM(DATENAME(MM, GETDATE()) +','+ RIGHT(CONVERT(VARCHAR(12), GETDATE() ,107),9))+ ')'

Gives
(Expires December, 19, 2012)

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1398240

Minnu

Posted Wednesday, December 19, 2012 2:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

Thank 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...

Post #1398247

anthony.green

Posted Wednesday, December 19, 2012 2:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

You 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 ','

Post #1398248

Minnu

Posted Wednesday, December 19, 2012 8:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

Hi Anthony,

Small change in my requirement, I want only one comma after the Date.

Product : Vehicles (Expires September 30, 2012)

Post #1398438

anthony.green

Posted Wednesday, December 19, 2012 8:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

then do what you where doing in the first place, this is how it outputs

SELECT '(Expires '+RTRIM(DATENAME(MM, GETDATE() ) + RIGHT(CONVERT(VARCHAR(12), GETDATE() ,107),9))+ ')'

(Expires December 19, 2012)

Post #1398441

Minnu

Posted Wednesday, December 19, 2012 8:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

First QuerySET @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)

Second QuerySELECT '(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, 2012

Post #1398442

anthony.green

Posted Wednesday, December 19, 2012 8:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Well 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_date

Post #1398444

Minnu

Posted Wednesday, December 19, 2012 8:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

Expiry_date Format is : DATETIME

Post #1398446

anthony.green

Posted Wednesday, December 19, 2012 8:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

DECLARE @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)

Post #1398449

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions