Post reply

Creating new table with formatted datetime type field

  • July 7, 2008 at 4:35 pm

    #189218

    I want to create a table X1 with a datetime field StartDate and format it as "dd-mmm-yyyy hh:mm:ss". Is defining the format of a field possible?

  • July 7, 2008 at 6:41 pm

    #839237

    From Books on Line (BOL)

    Date and Time (Transact-SQL)

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9bd1cc5b-227b-4032-95d6-7581ddcc9924.htm

    Then more from BOL

    CAST and CONVERT (Transact-SQL)

    specifically CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

    Remember unless you use a user defined data type a column is defined using a Data Type in your case DateTime. You format the information not in the column but when displayed using the Convert function. I would think that a style value of either 13 or 113 would be adequate

    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]

  • July 7, 2008 at 7:32 pm

    #839241

    alijaffaromran (7/7/2008)

    I want to create a table X1 with a datetime field StartDate and format it as "dd-mmm-yyyy hh:mm:ss". Is defining the format of a field possible?

    For way to many reasons to list all of them here, do NOT format a datetime in a table... like BitBucket said above, only do that during display... never store formatted anything... 😉

    If you serve different timezones, you need to change from formatted date back to datetime, add the timezone offset, and then reformat again if you have formatted dates.

    If the requirements change on the format, you need to change everything instead of just a simple display.

    You can't do date math on formatted dates without at least an implicit conversion.

    You can only sort certain formats but it's still a bad idea.

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

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