December 22, 2009 at 1:13 pm
Our vended cache database data source periodically sends us call data that contains negative numbers in the time call began field. This happens when a call is made close to midnight on one day and ends just past midnight on the next day. These types of calls cause our package to error out.
Presently our code looks like this and works fine as long as the call finishes before midnight.
SELECT cast (Time_Call_Began as timestamp) as Time_Call_Began
FROM CALL_TABLE
How would I be able to extract the call began time without erroring out when the time call began starts on one day and ends on the next? The input datatype is datetime. The SQL I'm attempting to use is below. The problem is it displays 0's for the time instead of the actual time. I don't care about the date portion of the field, only the time. The date portion is input to us in another field without the time.
SELECT dateadd(s, datediff(s,'1900-01-01',Time_Call_Began), '1900-01-01') AS Time_Call_Began
FROM CALL_TABLE
Thanks!
December 22, 2009 at 1:30 pm
Some sample data would help, both good and bad, along with the expected results based on the sample data.
December 22, 2009 at 2:58 pm
Good & expected data in TIME_CALL_BEGAN field:
2009-12-15 09:30:19.000
Bad data: -09384593 (note: this is a made up number similar to what we encounter. It doesn't correspond to the good data above. The bad data is difficult for us to see).
December 22, 2009 at 3:18 pm
bigclick (12/22/2009)
Good & expected data in TIME_CALL_BEGAN field:2009-12-15 09:30:19.000
Bad data: -09384593 (note: this is a made up number similar to what we encounter. It doesn't correspond to the good data above. The bad data is difficult for us to see).
So, TIME_CALL_BEGIN actually holds a datetime value when it is good? Would help if you had a bad value that you knew the correct time value for, hard to figure out from an arbitrary value.
December 23, 2009 at 7:23 am
Our vendor was able to give us an example.
Call Begin Date: 12/03/2009
Call Begin Time: -100
Call End Date: 12/04/2009
Call End Time: 12:05:00
Thanks for your help.
December 23, 2009 at 9:12 am
bigclick (12/23/2009)
Our vendor was able to give us an example.Call Begin Date: 12/03/2009
Call Begin Time: -100
Call End Date: 12/04/2009
Call End Time: 12:05:00
Thanks for your help.
What time is -100??
You should ask your vendor what time that call started within a range from 00:00:00.000 to 23:59:59.999. A value of -100 wouldn't fit in at all...
Or, you could ask him, how long that call was 🙂
December 23, 2009 at 9:37 am
Yes, it would make sense to have the actual call begin time. Thanks again for your help.
Call Begin Date: 12/03/2009
Call Begin Time: 11:58:20 pm
Value sent to us: -100
Call End Date: 12/04/2009
Call End Time: 12:05:00 am
December 23, 2009 at 9:38 am
bigclick (12/23/2009)
Our vendor was able to give us an example.Call Begin Date: 12/03/2009
Call Begin Time: -100
Call End Date: 12/04/2009
Call End Time: 12:05:00
Thanks for your help.
Now, to clarify, a good record would have a valid date and time in each field with the end date & time greater than the begin date & time, correct?
December 23, 2009 at 9:42 am
bigclick (12/23/2009)
Yes, it would make sense to have the actual call begin time. Thanks again for your help.Call Begin Date: 12/03/2009
Call Begin Time: 11:58:20 pm
Value sent to us: -100
Call End Date: 12/04/2009
Call End Time: 12:05:00 am
The value, -100, is the number of seconds you need to subtract from midnight. In this case 2009-12-04 00:00:00.000.
December 23, 2009 at 9:45 am
Yes, a valid record will have the beginning and ending date and call times populated. The beginning date and call time will be less than the ending data and call time.
Can the statement below be tweaked to subtract the -100 seconds or is there another statement that will work?
SELECT dateadd(s, datediff(s,'1900-01-01',Time_Call_Began), '1900-01-01') AS Time_Call_Began
FROM CALL_TABLE
December 23, 2009 at 10:00 am
I would use a CASE statement to test for Call_BeginTime being a numeric value or a datetime value and do the math based on that.
Here's an example using variables:
DECLARE
@Call_BeginDate char(10),
@Call_BeginTime char(11),
@Call_EndDate char(10),
@Call_EndTime char(11)
SET @Call_BeginDate ='12/03/2009'
SET @Call_BeginTime ='11:58:20 pm'
--SET @Call_BeginTime ='-100'
SET @Call_EndDate ='12/04/2009'
SET @Call_EndTime ='12:05:00 am'
SELECT
datediff(ss,
CASE
WHEN isnumeric(@Call_BeginTime) = 1
THEN dateadd(ss,
cast(@Call_BeginTime AS int),
cast(@Call_EndDate + ' 00:00:00' AS datetime)
)
ELSE cast(@Call_BeginDate+ ' ' + @Call_BeginTime AS datetime)
END,
cast(@Call_EndDate+ ' ' + @Call_EndTime AS datetime)
)
/* result: integer value of 400 in both cases
To switch between the two types for @Call_BeginTime values comment/uncomment the related two lines
*/
December 23, 2009 at 2:32 pm
Thanks for the CASE statement advice. When I run it to select from the source however, it doesn't like the ISNUMERIC. The data source is from a cache database so all the features of TSQL don't seem to work.
It returns the following:
1. ERROR
DS-DBMS-E400: UDA driver reported the following on connection 'xxxxx_Test':
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[Cache ODBC][State : S1000][Native Code 359]
[C:\Program Files\cognos\c8\bin\dsdesign.exe]
[SQLCODE: <-359>:<SQL Function (function Stored Procedure) not found>]
[Cache Error: <<SYNTAX>errdone+1^%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #359: User defined SQL Function 'SQLUSER.ISNUMERIC' does not exist>]
December 23, 2009 at 2:49 pm
Can you please post the code you are running? I know nothing < * using my best Sgt Shultz imitation * > about Cache or how you are accessing the data. Seeing how you are quering the data may help us come up with a solution (or not, but doesn't hurt).
December 23, 2009 at 3:04 pm
Also, are you running this code in the Cache database or via a linked server?
December 23, 2009 at 4:08 pm
bigclick (12/23/2009)
Thanks for the CASE statement advice. When I run it to select from the source however, it doesn't like the ISNUMERIC. The data source is from a cache database so all the features of TSQL don't seem to work.
Aside of Lynns question on how you query the data:
What would happen if you'd store the data "as is" into a SQL Server staging table and go from there?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply