December 19, 2012 at 1:37 am
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
December 19, 2012 at 2:06 am
SELECT '(Expires '+RTRIM(DATENAME(MM, GETDATE()) +','+ RIGHT(CONVERT(VARCHAR(12), GETDATE() ,107),9))+ ')'
Gives
(Expires December, 19, 2012)
December 19, 2012 at 2:32 am
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...
December 19, 2012 at 2:34 am
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 ','
December 19, 2012 at 8:15 am
Hi Anthony,
Small change in my requirement, I want only one comma after the Date.
Product : Vehicles (Expires September 30, 2012)
December 19, 2012 at 8:17 am
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)
December 19, 2012 at 8:21 am
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
December 19, 2012 at 8:24 am
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
December 19, 2012 at 8:27 am
Expiry_date Format is : DATETIME
December 19, 2012 at 8:29 am
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)
December 19, 2012 at 8:40 am
i 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...
December 19, 2012 at 8:43 am
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
Output is
VehiclesVehicles (Expires December 25, 2012)
December 19, 2012 at 8:50 am
How about this:
DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003';
SELECT DATENAME(MONTH, @Expiry_Date) + ' ' + DATENAME(DAY, @Expiry_Date) + ', ' + DATENAME(YEAR, @Expiry_Date);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 9:12 am
Or this:
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
December 19, 2012 at 6:11 pm
There's always alternate ways of formatting a date. Whether you'd want to use them is another story.
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)) + ')'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply