Post reply

Date format

  • December 19, 2012 at 1:37 am

    #271719

    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

    #1569932

    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

    #1569939

    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

    #1569940

    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

    #1570106

    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

    #1570109

    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

    #1570110

    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

    #1570112

    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

    #1570114

    Expiry_date Format is : DATETIME

  • December 19, 2012 at 8:29 am

    #1570116

    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

    #1570128

    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

    #1570132

    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

    #1570136

    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

    #1570155

    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

    #1570356

    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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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