SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


datetime stored as integer, please help!


datetime stored as integer, please help!

Author
Message
ericb1
ericb1
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 337
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!
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31321 Visits: 9730
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
Nabha
Nabha
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1814
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




---------------------------------------------------------------------------------
Nabha
Nabha
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1814
ohh sorry, dint see Gsquared's response!

---------------------------------------------------------------------------------
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31321 Visits: 9730
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
Nabha
Nabha
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1814
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 Smile Functions on any column having an index is not suggested if you are looking for better performance

---------------------------------------------------------------------------------
ericb1
ericb1
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 337
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


Nabha
Nabha
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 1814
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



---------------------------------------------------------------------------------
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31321 Visits: 9730
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
ericb1
ericb1
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 337
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),
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search