TSQL to combine a date field and a time field

Question

Using logparser to import IIS logs to a db results in one column that has the date value and a second field for time:

2010-05-25 00:00:00.000

and

2010-01-01 11:11:58.000

I'd like to code an after insert trigger that combines the 2 fields.

Answer 1

You can just add the two values after casting them to DATE and TIME datatypes, if you're using SQL Server 2008 or later. Here's an example.

declare @datet datetime;
set @datet = GETDATE();

select 
    @datet, 
    cast(@datet as date), 
    cast(@datet as time);

select 
    cast(cast(@datet as date) as datetime), 
    cast(cast(@datet as time) as datetime), 
    cast(cast(@datet as date) as datetime) + cast(cast(@datet as time) as datetime);

Answer 2

In case somebody else stumbles on this thread (or in case the original poster still can use this answer), look into the TO_TIMESTAMP ( date , time ) function in LogParser which lets you combine a date-only timestamp with a time-only timestamp into a full timestamp value... and saves you from having to convert in the db...

Answer 3

Try this:

DECLARE @Date varchar(23)
       ,@Time varchar(23)
       ,@Both datetime

SELECT @Date='2010-05-25 00:00:00.000'
      ,@Time='2010-01-01 11:11:58.000'

SELECT @Both=LEFT(@Date,10)+' '+RIGHT(@Time,12)

SELECT @Both

OUTPUT:

-----------------------
2010-05-25 11:11:58.000

(1 row(s) affected)

Set based:

DECLARE @INSERTED table(RowID int, DateOf varchar(23), TimeOf varchar(23), DateTimeOf datetime)

INSERT @INSERTED VALUES (1,'2010-05-25 00:00:00.000','2010-01-01 11:11:58.000',null)
INSERT @INSERTED VALUES (2,'2010-04-05 00:00:00.000','2010-01-01 12:34:56.789',null)
INSERT @INSERTED VALUES (3,'2010-03-15 00:00:00.000','2010-01-01 01:01:01.000',null)


UPDATE @INSERTED
    SET DateTimeOf=LEFT(DateOf,10)+' '+RIGHT(TimeOf,12)

SELECT * FROM @INSERTED

OUTPUT:

RowID   DateOf                  TimeOf                  DateTimeOf
------- ----------------------- ----------------------- -----------------------
1       2010-05-25 00:00:00.000 2010-01-01 11:11:58.000 2010-05-25 11:11:58.000
2       2010-04-05 00:00:00.000 2010-01-01 12:34:56.789 2010-04-05 12:34:56.790
3       2010-03-15 00:00:00.000 2010-01-01 01:01:01.000 2010-03-15 01:01:01.000

(3 row(s) affected)