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

Answer 1

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

 

 

Answer 2

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

 

Answer 3

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.