Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Date format Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 1:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
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
Posted Wednesday, December 19, 2012 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
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
Posted Wednesday, December 19, 2012 2:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
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
Posted Wednesday, December 19, 2012 2:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
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 ','



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 #1398248
Posted Wednesday, December 19, 2012 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
Hi Anthony,

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


Product : Vehicles (Expires September 30, 2012)
Post #1398438
Posted Wednesday, December 19, 2012 8:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
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)




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 #1398441
Posted Wednesday, December 19, 2012 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713
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
Posted Wednesday, December 19, 2012 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
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




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 #1398444
Posted Wednesday, December 19, 2012 8:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:09 AM
Points: 227, Visits: 713

Expiry_date Format is : DATETIME
Post #1398446
Posted Wednesday, December 19, 2012 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
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)




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 #1398449
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse