October 15, 2009 at 7:22 am
Using SQL2005, I'm importing call data from my Cisco Communications Manager. The data is a text file, and the date/time fields are in a numerical, integer format (UNIX EPOH format): 1251781074
Cisco told me to convert that to a readable DATETIME format with this formula:
myfield = DATEADD(hour,-5,(dateadd(second ,myfield, '1/1/1970')))
This works correctly, but since the data is stored as an INT field, I cannot put a clause on my results. So I'm trying to do something like this:
DECLARE @StartDateTime DATETIME
,@EndDateTime DATETIME
SELECT @StartDateTime = '10/13/2009 08:00'
SELECT @EndDateTime = '10/13/2009 23:00'
...
WHERE
DATEADD(hour,-4,(dateadd(second ,myfield, '1/1/1970'))) >= @StartDateTime
AND DATEADD(hour,-4,(dateadd(second ,myfield, '1/1/1970'))) <= @EndDateTime
I tried changing the fields to DATETIME but it will not let me, and since the fields are INT, it's ignoring my variables in DATETIME format.
Is there any way to set this field to a DATETIME so that I can set the WHERE clause on it? Or, format the variable into an INT to compare?
I tried setting the variable to: SELECT @StartDateTime = 1251781074 but that won't work either.
Any help is greatly appreciated, thanks!
October 15, 2009 at 7:50 am
Try this:
declare @StartInt int,
@EndInt int;
select @StartInt = datediff(second, dateadd(hour, -4, '1/1/1970'), @StartDateTime),
@EndInt = datediff(second, dateadd(hour, -4, '1/1/1970'), @EndDateTime);
Where MyField >= @StartInt and MyField <= @EndInt;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 7:53 am
Sorry if I've not understood ur requirement, but does this work?
drop table t1
Create table t1 (tim int, rem varchar(100))
select 86400
INSERT INTO t1 VALUES (1251781074, 'day1')
INSERT INTO t1 VALUES (1251781074 + 86400, 'day2')
INSERT INTO t1 VALUES (1251781074 + 2*86400, 'day3')
INSERT INTO t1 VALUES (1251781074 + 3*86400, 'day4')
INSERT INTO t1 VALUES (1251781074 + 4*86400, 'day5')
Select DATEADD(hour,-4,(dateadd(second ,tim, '1/1/1970'))), * From t1
DECLARE @StartDateTime DATETIME
,@EndDateTime DATETIME
SELECT @StartDateTime = '2009-09-02 00:57:54.000'
SELECT @EndDateTime = '2009-09-03 00:57:54.000 '
Select * from t1
Where
DATEADD(hour,-4,(dateadd(second ,tim, '1/1/1970'))) >= @StartDateTime
AND DATEADD(hour,-4,(dateadd(second ,tim, '1/1/1970'))) <= @EndDateTime
---------------------------------------------------------------------------------
October 15, 2009 at 7:55 am
ohh sorry, dint see Gsquared's response!
---------------------------------------------------------------------------------
October 15, 2009 at 7:56 am
One reason I worked it the other direction is that it gets rid of the functions in the Where clause. That's usually a good idea for performance. Probably doesn't matter in this case, but it's a good habit to be in.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 8:01 am
GSquared (10/15/2009)
One reason I worked it the other direction is that it gets rid of the functions in the Where clause. That's usually a good idea for performance. Probably doesn't matter in this case, but it's a good habit to be in.
ah yes thats true 🙂 Functions on any column having an index is not suggested if you are looking for better performance
---------------------------------------------------------------------------------
October 15, 2009 at 8:46 am
Thanks so much for the help, I really appreciate it. Here's what I've got, but I'm gettig an error on the conversion:
Msg 8115, Level 16, State 2, Line 13
Arithmetic overflow error converting expression to data type datetime.
It's a pretty simple query, hitting a table of call logs, trying to find incoming and outgoing calls for an extension (386). Again, any help is greatly appreciated!
DECLARE @StartDateTime DATETIME
,@EndDateTime DATETIME,
@StartInt int,
@EndInt int;
SELECT @StartDateTime = '09/01/2009 01:00'
SELECT @EndDateTime = '10/13/2009 23:00'
select @StartInt = datediff(second, dateadd(hour, -4, '1/1/1970'), @StartDateTime),
@EndInt = datediff(second, dateadd(hour, -4, '1/1/1970'), @EndDateTime);
select DISTINCT callingPartyNumber AS 'Calling Number',
originalCalledPartyNumber AS 'Intended Called Number',
finalCalledPartyNumber AS 'Final Destination',
datetimeorigination = DATEADD(hour,-5,(dateadd(second ,dateTimeOrigination, '1/1/1970'))),
datetimedisconnect = DATEADD(hour,-5,(dateadd(second ,dateTimedisconnect, '1/1/1970'))),
duration AS 'Duration (seconds)'
from CDR
where finalcalledpartynumber = ('386') OR callingPartyNumber = ('386')
AND DATEADD(hour,-4,(dateadd(second ,dateTimeOrigination, '1/1/1970'))) >= @StartInt
AND DATEADD(hour,-4,(dateadd(second ,dateTimeOrigination, '1/1/1970'))) <= @EndInt
-- AND (duration) < 9
order by datetimedisconnect
October 15, 2009 at 9:08 am
Thats because you are mixing both of our suggestions!
try this in your where clause
where finalcalledpartynumber = ('386') OR callingPartyNumber = ('386')
AND dateTimeOrigination >= @StartInt
AND dateTimeOrigination<= @EndInt
---------------------------------------------------------------------------------
October 15, 2009 at 9:50 am
It probably actually needs to be:
where (finalcalledpartynumber = ('386') OR callingPartyNumber = ('386'))
AND dateTimeOrigination >= @StartInt
AND dateTimeOrigination<= @EndInt
Without the parentheses around the party numbers, it won't use the second half of the Where clause correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 10:49 am
thanks so much, that seems to work!
I had to change it to:
where
dateTimeOrigination >= @StartInt
AND dateTimeOrigination <= @EndInt
AND (finalcalledpartynumber = '386' OR callingPartyNumber = '386')
to get it to work correctly.
Also, in the conversion, is it suposed to be -4 or -5?
select @StartInt = datediff(second, dateadd(hour, -5, '1/1/1970'), @StartDateTime),
October 15, 2009 at 10:55 am
Whatever they told you to use. The confusion on that came from the first post, where it has a -5 in one place and -4 in another.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 10:56 am
I have an additional suggestion..
Put a computed column in the table that contains the formula and set it to persisted. Then instead of the int column you reference the computed column. And because it is persisted you should get pretty good performance on it. Then your queries don't have to contain that silly formula..
CEWII
October 16, 2009 at 7:26 am
Elliott W (10/15/2009)
I have an additional suggestion..Put a computed column in the table that contains the formula and set it to persisted. Then instead of the int column you reference the computed column. And because it is persisted you should get pretty good performance on it. Then your queries don't have to contain that silly formula..
CEWII
That will only work if it considers the computation deterministic. I'm not sure if it will or not on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 16, 2009 at 7:43 am
GSquared (10/16/2009)
Elliott W (10/15/2009)
I have an additional suggestion..Put a computed column in the table that contains the formula and set it to persisted. Then instead of the int column you reference the computed column. And because it is persisted you should get pretty good performance on it. Then your queries don't have to contain that silly formula..
CEWII
That will only work if it considers the computation deterministic. I'm not sure if it will or not on this.
The formula as presented should be deterministic, You put the value in and always get the same value out. But I would still go with the computed column because it would only be computed once for an access, I'm not sure when you repeatedly use the formula in the where clause.. If nothing else it would make sure that the formula is applied correctly EVERY time.
<soapbox>And for the record, I hate storing date/time in this manner..</soapbox>
CEWII
October 19, 2009 at 7:56 am
create table dbo.CompCol (
ID int identity primary key,
MyField int,
MyDate as dateadd(hour, -5, dateadd(second, MyField, '1/1/1970')));
go
create index IDX_CompCol_MyDate on dbo.CompCol (MyDate);
Result:
Msg 2729, Level 16, State 1, Line 1
Column 'MyDate' in table 'dbo.CompCol' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
DateAdd functions isn't considered deterministic by SQL Server because it depends on server settings, which can change.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply