Post reply

date format in sql

  • March 10, 2009 at 7:55 am

    #207280

    Hi can we have an alternative of

    CStr(Format([InvoiceDate],"dd-mmm-yyyy")) in SQL

    Many Thanks ....

  • March 10, 2009 at 8:48 am

    #956848

    Using Books On Line (BOL) look up CAST and CONVERT (Transact-SQL)

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm

    DECLARE @InvoiceDate DATETIME

    DECLARE @Ans AS VARCHAR(10)

    SET @InvoiceDate = '2-29-08'

    SET @Ans = CONVERT ( VARCHAR(12) , @InvoiceDate , 105 )

    SELECT @Ans

    which gives: 29-02-2008

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 10, 2009 at 10:45 am

    #956941

    but 29-02-2008 needs to be in 29-Feb-2008 format

  • March 10, 2009 at 10:49 am

    #956944

    How about the following:

    DECLARE @InvoiceDate DATETIME

    DECLARE @Ans AS VARCHAR(12)

    SET @InvoiceDate = '2-29-08'

    SET @Ans = CONVERT ( VARCHAR(12) , @InvoiceDate , 105 )

    SELECT @Ans

    SET @Ans = CONVERT ( VARCHAR(12) , @InvoiceDate , 113 )

    SELECT @Ans

  • March 10, 2009 at 3:28 pm

    #957114

    Hi

    If you don't need the conversion for direct inserts or updates into other tables maybe think about formattin in your client application ;).

    Greets

    Flo

  • March 10, 2009 at 3:42 pm

    #957121

    Hello nabajyoti.b,

    Just to notice, there is NO such format as you wish.

    The nearest format is the previously mentioned, but you need modification

    DECLARE @InvoiceDate DATETIME

    DECLARE @Ans AS VARCHAR(12)

    SET @InvoiceDate = '3-11-09'

    SET @Ans = CONVERT ( VARCHAR(12) , @InvoiceDate , 113 )

    --This gives '11 Mar 2009', all you need is to replace spaces with '-'

    --and here it is

    SELECT @Ans

    SET @Ans = REPLACE(CONVERT ( VARCHAR(12) , @InvoiceDate , 113 ),' ','-')

    SELECT @Ans

    --The result is '11-Mar-2009'

  • March 10, 2009 at 8:15 pm

    #957218

    But, again, and as Flo suggested... SQL Server is NOT the place to do such formatting unless it's for direct output to a file sans any trip through a GUI or for reporting being done on the server (again, sans any GUI or reporting software). There are many reasons but one of them is local... if you simply send the date back as a datetime datatype, the app will likely be able to automatically format the date based on local requirements setup on the desktop. If you brute force the format into a string, like you're doing, either the conversion must be reversed to be displayed correctly, or you end up with an incorrect display.

    So... what are you going to use this display related conversion for? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply