Results 1 to 6 of 6

Thread: varchar to Date

  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: varchar to Date

    This is probably a very simple question, but there it goes.

    I have a DB table with the following varchar value:
    1/12/2004

    I want to convert the specified data to the following:
    Monday, January 12th, 2004

    I guess i want to:
    Convert(DATETIME, '1/12/2004'), But...
    I want it to be inserted into the new table as a long date type value.

    Is this possible?


    Thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dates are stored in sql sever as a number...the rest is just presentation stuff...

    Just convert the value to date and insert it to your new date column...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    whatever way you insert the value, datetime will always be stored as yyyy-mm-dd hh:mm:ss.sss

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually it's stored as 2 4 byte integers


    BOL:


    Remarks
    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is not stored as yyyy-mm-dd hh:mm:ss.sss. It is stored as a numeric value. yyyy-mm-dd hh:mm:ss.sss is just one way of formatting the value.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2004
    Posts
    2
    I guess my Convert statement will do the trick then.

    Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •