none
sql server 2008: add time + date

    Question

  • Hi experts,

     

    table:

    C_ID | DATE | TIME

    1 | 2000-01-01 | 12:00

    1 | 2000-01-01 | 23:00

    1 | 2000-01-02 | 09:00

    1 | 2000-01-02 | 13:00

    1 | 2001-01-02 | 11:00

     

    I group this table by c_id but I want to receive the latest datetime of the customer. In this case i the result should be:

     

    1 | 2000-01-02 | 13:00

     

    I thought about doing that with MAX(DATE+TIME) but that doesn't work as the add operator is invalid here.

     

    Any help is appriciated. Regards

    Friday, November 14, 2008 3:35 PM

Answers

  • You can try this to merge Date and Time columns.

    Code Snippet

    CONVERT(NVARCHAR(19), CONVERT(VARCHAR(12), myDateOnly)

    + ' ' + CONVERT(VARCHAR(12), myTimeOnly), 101)

    AS DateOnlyPlusTimeOnlyToNewDateTime

     

    --Example:

    SELECT CONVERT(NVARCHAR(19), CONVERT(VARCHAR(12), CAST(getdate() as DATE))

    + ' ' + CONVERT(VARCHAR(12), CAST(getdate() as Time)), 101)

    AS DateOnlyPlusTimeOnlyToNewDateTime

     

     

    Friday, November 14, 2008 3:55 PM
    Moderator
  • Try this:

     

    create table #temp

    (id int, tdate varchar(10), ttime varchar(5))

    insert into #temp

    select 1,'2000-01-01','12:00'

    union all

    select 1,'2000-01-01','23:00'

    union all

    select 1,'2000-01-02','09:00'

    union all

    select 1,'2000-01-02','13:00'

    union all

    select 1,'2000-01-02','11:00'

    select id,max(convert(varchar(20), tdate + ' ' + ttime )) date

    from #temp

    group by id

    go

    drop table #temp

     

    Saturday, November 15, 2008 2:01 AM
  • Hi:

     

       Here is another solution:

     

    Code Snippet

     

     

    DECLARE @t TABLE
    (
       
    ID INT,
       
    [Date] date,
       
    [Time] time
    )

    INSERT @t SELECT 1,'2008-1-1','12:00'
    UNION ALL SELECT 1,'2008-1-1','23:00'
    UNION ALL SELECT 1,'2008-1-2','09:00'
    UNION ALL SELECT 1,'2008-1-2','13:00'
    UNION ALL SELECT 1,'2008-1-2','11:00'
    UNION ALL SELECT 2,'2008-1-1','13:00'
    UNION ALL SELECT 2,'2008-1-1','12:00'


    SELECT
       
    a.*
    FROM @t a
    WHERE NOT EXISTS(
       
    SELECT
           
    *
       
    FROM @t
       
    WHERE ID a.ID
           
    AND [Date] <= a.[Date]
           
    AND [Time] a.[Time] 
    )

     

     

    Hope it helps.

    Saturday, November 15, 2008 5:28 AM
    Moderator

All replies

  • You can try this to merge Date and Time columns.

    Code Snippet

    CONVERT(NVARCHAR(19), CONVERT(VARCHAR(12), myDateOnly)

    + ' ' + CONVERT(VARCHAR(12), myTimeOnly), 101)

    AS DateOnlyPlusTimeOnlyToNewDateTime

     

    --Example:

    SELECT CONVERT(NVARCHAR(19), CONVERT(VARCHAR(12), CAST(getdate() as DATE))

    + ' ' + CONVERT(VARCHAR(12), CAST(getdate() as Time)), 101)

    AS DateOnlyPlusTimeOnlyToNewDateTime

     

     

    Friday, November 14, 2008 3:55 PM
    Moderator
  • Try this:

     

    create table #temp

    (id int, tdate varchar(10), ttime varchar(5))

    insert into #temp

    select 1,'2000-01-01','12:00'

    union all

    select 1,'2000-01-01','23:00'

    union all

    select 1,'2000-01-02','09:00'

    union all

    select 1,'2000-01-02','13:00'

    union all

    select 1,'2000-01-02','11:00'

    select id,max(convert(varchar(20), tdate + ' ' + ttime )) date

    from #temp

    group by id

    go

    drop table #temp

     

    Saturday, November 15, 2008 2:01 AM
  • Hi:

     

       Here is another solution:

     

    Code Snippet

     

     

    DECLARE @t TABLE
    (
       
    ID INT,
       
    [Date] date,
       
    [Time] time
    )

    INSERT @t SELECT 1,'2008-1-1','12:00'
    UNION ALL SELECT 1,'2008-1-1','23:00'
    UNION ALL SELECT 1,'2008-1-2','09:00'
    UNION ALL SELECT 1,'2008-1-2','13:00'
    UNION ALL SELECT 1,'2008-1-2','11:00'
    UNION ALL SELECT 2,'2008-1-1','13:00'
    UNION ALL SELECT 2,'2008-1-1','12:00'


    SELECT
       
    a.*
    FROM @t a
    WHERE NOT EXISTS(
       
    SELECT
           
    *
       
    FROM @t
       
    WHERE ID a.ID
           
    AND [Date] <= a.[Date]
           
    AND [Time] a.[Time] 
    )

     

     

    Hope it helps.

    Saturday, November 15, 2008 5:28 AM
    Moderator