Results 1 to 7 of 7

Thread: Update a datetime column

  1. #1
    Join Date
    Apr 2005
    Posts
    7

    Question Update a datetime column

    I'm trying to update a datetime column from another datetime column. However, I just want the date transferred to the new column without the time. Any ideas? Thanks for your help.

  2. #2
    Join Date
    Apr 2005
    Posts
    3
    Try this

    Update Table set DateTimeField = DateAdd(day,DateDiff(day,DateTimeField,OtherDateTi meField),DateTimeField)

    Basically you add to your field the delta in days between the two datetime fields

    Robert

  3. #3
    Join Date
    Apr 2005
    Posts
    7
    I'm trying to populate a new datetime column with the mm/dd/yyyy from another datetime column that has mm/dd/yyyy hh:mm:ss AM/PM. Is there a way to update this empty column with just the mm/dd/yyyy?

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    You can just use CONVERT:

    CONVERT(char(10), YourDateTimeColumn, 101)

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    Nosepicker's suggestion is right on the money, but you should realize that SQL Server has no concept of a date without a time. If you enter just a date with no time, SQL Server assumes that the time is midnight. So if you update the column with a date of 4/21/2005, SQL Server see it as 4/21/2005 0:00:00.0000 AM.

    This distinction is very important when you start comparing date values.

  6. #6
    Join Date
    Apr 2005
    Posts
    7
    Thank you for your help. I wish SQL would incorporate two additional field types (Date Only, Time Only). Changing the date to CHR will not allow a query structure such as "DATE > 2005/01/02".

  7. #7
    Join Date
    Feb 2003
    Posts
    1,048
    I agree, I wish there was date only and time only data types. You can do a query where you look simply say greater than the date value, but again, SQL Server will assume that you mean 12 AM on that date.

Posting Permissions

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