locked
date-field and time-field into one field query RRS feed

  • Question

  • Hello,

    I am looking for a query to put the date from one field (datetime) and the time from another field (datetime), into a third field (also datetime).

    Is this possible within one query, so that I can update the whole table with it.

    Thanks!
    Monday, June 9, 2008 2:09 PM

Answers

  • Welcome to the Sql forumns; there are a number of ways to do this.  A general method is along the line of:

    • breaking the date from the first datetime field
    • breaking the time from the second datetime field
    • Combining the two to form the new datetime field

    There are several ways for breaking the fields:

    • The date and time can be split out into strings using a CONVERT functon (see CAST AND CONVERT in books online)
    • The date can also be obtained by converting the datetime field to a FLOAT and applying the FLOOR function to the converted float
    • The time can be obtained as the fractional part of the datetime converted to a FLOAT datatype
    • The date and time can also be obtained by converting the timestamp to binary and manipulating the binary (less intuitive and no good reason to do it this way)

    EDIT:

     

    A couple of examples:

     

    Code Snippet

    declare @dt1 datetime     set @dt1 = '20080101 13:15'
    declare @dt2 datetime     set @dt2 = '20080609 08:21'

     

    select
      @dt1 [first datetime],
      @dt2 [second datetime],
      convert(datetime, convert(varchar(10), @dt1, 112)
        + ' ' + convert(varchar(12), @dt2, 114))
      as [first date / second time]

     

    /* -------- Sample Output: --------
    first datetime          second datetime         first date / second time
    ----------------------- ----------------------- ------------------------
    2008-01-01 13:15:00.000 2008-06-09 08:21:00.000 2008-01-01 08:21:00.000
    */

     

    select
      @dt1 [first datetime],
      @dt2 [second datetime],
      cast( floor(cast(@dt1 as float))
            + cast(@dt2 as float) - floor(cast(@dt2 as float))
            as datetime)
      as [first date / second time]

     

    /* -------- Sample Output: --------
    first datetime          second datetime         first date / second time
    ----------------------- ----------------------- ------------------------
    2008-01-01 13:15:00.000 2008-06-09 08:21:00.000 2008-01-01 08:20:59.997
    */

     

    It appears that the first method is more accurate than the second method.

     

    ( Help! Did I do something wrong with the 2nd example? )

    Monday, June 9, 2008 4:28 PM
  • You can also try something like this for an update Statement:


    Update tblDates
    Set Date3 =(
    Select
    (Cast(DatePart(m,Date1)AS VARCHAR) + '/'+
    CAST(DatePart(dd,Date1) AS VARCHAR) + '/' +
    CAST(DatePart(yy,Date1) AS VARCHAR)+' '+
    CAST(DatePart(hh,Date2)AS VARCHAR) +  ':' +
    CAST(DatePart(n,Date2) AS VARCHAR))
    FROm tblDates WHERE X = 'Y')

    This will parse out the information you need from Each field and combine it all together for your 3rd date field.

    Thursday, June 12, 2008 11:39 PM

All replies

  • Welcome to the Sql forumns; there are a number of ways to do this.  A general method is along the line of:

    • breaking the date from the first datetime field
    • breaking the time from the second datetime field
    • Combining the two to form the new datetime field

    There are several ways for breaking the fields:

    • The date and time can be split out into strings using a CONVERT functon (see CAST AND CONVERT in books online)
    • The date can also be obtained by converting the datetime field to a FLOAT and applying the FLOOR function to the converted float
    • The time can be obtained as the fractional part of the datetime converted to a FLOAT datatype
    • The date and time can also be obtained by converting the timestamp to binary and manipulating the binary (less intuitive and no good reason to do it this way)

    EDIT:

     

    A couple of examples:

     

    Code Snippet

    declare @dt1 datetime     set @dt1 = '20080101 13:15'
    declare @dt2 datetime     set @dt2 = '20080609 08:21'

     

    select
      @dt1 [first datetime],
      @dt2 [second datetime],
      convert(datetime, convert(varchar(10), @dt1, 112)
        + ' ' + convert(varchar(12), @dt2, 114))
      as [first date / second time]

     

    /* -------- Sample Output: --------
    first datetime          second datetime         first date / second time
    ----------------------- ----------------------- ------------------------
    2008-01-01 13:15:00.000 2008-06-09 08:21:00.000 2008-01-01 08:21:00.000
    */

     

    select
      @dt1 [first datetime],
      @dt2 [second datetime],
      cast( floor(cast(@dt1 as float))
            + cast(@dt2 as float) - floor(cast(@dt2 as float))
            as datetime)
      as [first date / second time]

     

    /* -------- Sample Output: --------
    first datetime          second datetime         first date / second time
    ----------------------- ----------------------- ------------------------
    2008-01-01 13:15:00.000 2008-06-09 08:21:00.000 2008-01-01 08:20:59.997
    */

     

    It appears that the first method is more accurate than the second method.

     

    ( Help! Did I do something wrong with the 2nd example? )

    Monday, June 9, 2008 4:28 PM
  • You can also try something like this for an update Statement:


    Update tblDates
    Set Date3 =(
    Select
    (Cast(DatePart(m,Date1)AS VARCHAR) + '/'+
    CAST(DatePart(dd,Date1) AS VARCHAR) + '/' +
    CAST(DatePart(yy,Date1) AS VARCHAR)+' '+
    CAST(DatePart(hh,Date2)AS VARCHAR) +  ':' +
    CAST(DatePart(n,Date2) AS VARCHAR))
    FROm tblDates WHERE X = 'Y')

    This will parse out the information you need from Each field and combine it all together for your 3rd date field.

    Thursday, June 12, 2008 11:39 PM