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 2010-01-01 11:11:58.000
I'd like to code an INSERT AFTER trigger that combines the 2 fields.
views:
45answers:
2
+4
A:
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);
Dave Markle
2010-05-25 11:25:55
The Date and Time as datatypes seems much cleaner than pulling positions from a string. New to 08, you say. So you've got me the first 9 yards - i can update all rows in the table - how do I update just the inserted rows - looks like at a minimum i should be looking at 'INSERT BEFORE' instead of AFTER thx
justSteve
2010-05-25 11:54:40
If you can, avoid the trigger altogether and just add another column to the table as a computed column instead. You can do this by using s statement like this: ALTER TABLE yourtable ADD DateAndTime (cast(cast(@datet as date) as datetime) + cast(cast(@datet as time) as datetime));
Dave Markle
2010-05-25 12:16:26
+1
A:
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)
KM
2010-05-25 11:30:02