Post reply

Convert Date

  • June 4, 2009 at 10:12 am

    #131630

    I have a field at datetime 1/1/2009 12:00:00 AM, how can I convert it to 1/1/2009.Thank you

  • June 4, 2009 at 10:30 am

    #1004782

    Krasavita (6/4/2009)

    I have a field at datetime 1/1/2009 12:00:00 AM, how can I convert it to 1/1/2009.Thank you

    If you want in US standard i.e mm/dd/yyyy then

    select convert(varchar(10),'01/01/2009 12:00:00 AM',101)

    if you want is British\French standard i.e dd/mm/yyyy then

    select convert(varchar(10),'01/01/2009 12:00:00 AM',103)

  • June 4, 2009 at 10:50 am

    #1004796

    Where do I put the name of the field (not the data)

  • June 4, 2009 at 11:02 am

    #1004802

    Krasavita (6/4/2009)

    Where do I put the name of the field (not the data)

    Try putting the name of your field where the sample data was put in the post above.

  • June 4, 2009 at 11:08 am

    #1004805

    By the way, BOL (Books Online) is a great resource for just this type of question.

  • June 4, 2009 at 11:25 am

    #1004810

    Substitute data with field name please remove quotes.

  • June 4, 2009 at 11:52 am

    #1004818

    Hello.. Hello .. Hello .. Seems we got an echo in this thread.

  • June 4, 2009 at 12:03 pm

    #1004830

    Sorry didn't see your post and responded

  • June 4, 2009 at 12:24 pm

    #1004847

    Krasavita (6/4/2009)

    I have a field at datetime 1/1/2009 12:00:00 AM, how can I convert it to 1/1/2009.Thank you

    Once you have it converted, where will you use the converted date? Will you be storing it in a table?

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

  • June 4, 2009 at 12:39 pm

    #1004858

    Thank you

  • June 4, 2009 at 12:40 pm

    #1004859

    Yes, I will be storing in the table

  • June 4, 2009 at 12:51 pm

    #1004862

    Great call Jeff.

    If it's a datetime, and you'll be storing it in a table, why not just keep it as a datetime? Sql Server really works best when you store dates as their appropriate types. Plus it will make it easier on you when you have to query that column to retrieve records based on that column. Formatting the date like that is for the user, not for the database, so format it in the UI, or as it's presented to the UI, Don't reformat it and them store it formatted in the database. You'll just be asking for problems down the road.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • June 4, 2009 at 8:22 pm

    #1004990

    To add to what Luke has already stated, do a simple but powerful test that will prove that storing the formatted date in the table is absolutely the wrong thing to do. Try doing one of the simplest, yet, most powerful things you can do with a SELECT statement. Try sorting on the formatted date column and see the mess you end up with.

    Unless you have a secret "Death By SQL" wish, don't store formatted dates in a table... not even a reporting table.

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

  • June 4, 2009 at 8:43 pm

    #1004995

    Jeff Moden (6/4/2009)

    [...]don't store formatted dates in a table... not even a reporting table.

    [font="Verdana"]We could have an argument about that, but in general I believe you are correct. The exception (for me) is a Calendar table, but that's more to break a date into constituent parts and attributes. In most cases I believe you should leave formatting dates to the front end applications.[/font]

  • June 4, 2009 at 9:00 pm

    #1004998

    Bruce W Cassidy (6/4/2009)

    Jeff Moden (6/4/2009)

    [...]don't store formatted dates in a table... not even a reporting table.

    [font="Verdana"]We could have an argument about that, but in general I believe you are correct. The exception (for me) is a Calendar table, but that's more to break a date into constituent parts and attributes. In most cases I believe you should leave formatting dates to the front end applications.[/font]

    Heh... arguments aren't necessary. Just code. Other than a calendar table, do you have an example where storing a date in the format of 1/12/2009 in a table is actually the right thing to do?

    I'll just bet that except for day and month names, you store the consituent parts as INT, SMALLINT, and TINYINT and not CHAR or VARCHAR. Do you do things like store "Q1" or "M6"? I'm thinking that you don't.

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

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