Post reply

Fix date format in a query.. Split/Array?

  • April 2, 2009 at 11:24 am

    #130347

    Hi,

    So I have this table and one of the fields has a Date in it… but its not in needed format, it is YYYY-DD-MM and I need MM-DD-YYYY.

    Is there a way in TSQL when doing a query I can swop the data, kind of like doing a split on “-“ array but as inline TSQL?

    Thanks for any help

    Shane

  • April 2, 2009 at 12:04 pm

    #970982

    Look at the CONVERT function in Books On Line and review the effect on output of the style value

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

    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]

  • April 2, 2009 at 11:25 pm

    #971313

    shane (4/2/2009)

    Hi,

    So I have this table and one of the fields has a Date in it… but its not in needed format, it is YYYY-DD-MM and I need MM-DD-YYYY.

    Is there a way in TSQL when doing a query I can swop the data, kind of like doing a split on “-“ array but as inline TSQL?

    Thanks for any help

    Shane

    Shane... what is the data type of the column that has the date in it and, it may be important, where will you use the final formatted date?

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

  • April 3, 2009 at 8:25 am

    #971608

    Hi

    Check out the below link. You dont have to use Split. Its just that you need to use CONVERT function.

    http://www.sqljunkies.ddj.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • April 3, 2009 at 9:34 am

    #971681

    CONVERT alone will not help you.

    You need to do a little string manipulation.

    If your date field is a string

    DECLARE @Date VARCHAR(10)

    SET @Date = '2009-31-12' --YYYY-DD-MM

    SELECT @Date = LEFT(@Date, 5) + RIGHT(@DATE, 2) + SUBSTRING(@Date, 5, 3)

    SELECT REPLACE(CONVERT(CHAR(10), CONVERT(DATETIME, @Date, 120), 101), '/', '-')

    If your date field is a datetime (which I doubt because datetime is format independent) then less work:

    DECLARE @Date SMALLDATETIME

    SET @Date = '2009-12-31'

    SELECT REPLACE(CONVERT(CHAR(10), @Date, 101), '/', '-')

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • April 3, 2009 at 9:35 am

    #971688

    Maybe... but you don't ever want to send dates to the GUI as VARCHARS if you don't need to. Send it as a date/time so the local settings can be used to properly format the date.

    If it's for a file, then, yeah, I agree... CONVERT is the way to go.

    --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 6 posts - 1 through 5 (of 5 total)

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