Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

datetime stored as integer, please help! Expand / Collapse
Author
Message
Posted Thursday, October 15, 2009 7:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 16, 2014 12:39 PM
Points: 93, Visits: 300
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!

Post #803419
Posted Thursday, October 15, 2009 7:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #803449
Posted Thursday, October 15, 2009 7:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
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




---------------------------------------------------------------------------------
Post #803454
Posted Thursday, October 15, 2009 7:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
ohh sorry, dint see Gsquared's response!

---------------------------------------------------------------------------------
Post #803457
Posted Thursday, October 15, 2009 7:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #803459
Posted Thursday, October 15, 2009 8:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
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


---------------------------------------------------------------------------------
Post #803463
Posted Thursday, October 15, 2009 8:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 16, 2014 12:39 PM
Points: 93, Visits: 300
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

Post #803503
Posted Thursday, October 15, 2009 9:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
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



---------------------------------------------------------------------------------
Post #803524
Posted Thursday, October 15, 2009 9:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #803569
Posted Thursday, October 15, 2009 10:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 16, 2014 12:39 PM
Points: 93, Visits: 300
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),
Post #803600
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse