December 3, 2014 at 5:21 pm
Hi all,
I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.
0 = 00:00
30 = 00:30
...
1700 = 1700 etc...
How can I convert this to a real time field?
December 3, 2014 at 5:26 pm
SELECT CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), '30'), 4), 3, 0, ':'))
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 3, 2014 at 5:33 pm
craig.bobchin (12/3/2014)
Hi all,I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.
0 = 00:00
30 = 00:30
...
1700 = 1700 etc...
How can I convert this to a real time field?
Have a look at the dbo.agent_datetime function that's built into MSDB. If you need this to run a lot, you should probably steal a copy and modify that copy to be an iTVF instead of a scalar UDF.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2014 at 5:49 pm
Thanks that worked. How can I concatenate this with a date field. neither '&' nor '+' seem to work.
December 3, 2014 at 5:53 pm
craig.bobchin (12/3/2014)
Thanks that worked. How can I concatenate this with a date field. neither '&' nor '+' seem to work.
Append it before you turn it into a TIME field. So, convert the datetime to varchar, leave the Time as varchar, append them with +, then convert the entire mess back to datetime.
Side note: This thing will be horrid for any kind of parameterization usage, such as ranges. Just FYI.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 3, 2014 at 5:58 pm
Evil Kraig F (12/3/2014)
SELECT CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), '30'), 4), 3, 0, ':'))
Thanks. I'll take a look at it.
December 3, 2014 at 6:25 pm
Evil Kraig F (12/3/2014)
craig.bobchin (12/3/2014)
Thanks that worked. How can I concatenate this with a date field. neither '&' nor '+' seem to work.Append it before you turn it into a TIME field. So, convert the datetime to varchar, leave the Time as varchar, append them with +, then convert the entire mess back to datetime.
Side note: This thing will be horrid for any kind of parameterization usage, such as ranges. Just FYI.
This gives me the following error after flashing the resultset in a grid: Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I've tried posing the query but I keep getting an error on SQLcentral.com
December 3, 2014 at 6:30 pm
That's more likely an issue with the data than with the code.
Shouldn't be getting an error from SSC for posting code, I do it constantly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 3, 2014 at 6:35 pm
Attached is the actual query I'm trying to use.
December 3, 2014 at 8:18 pm
Got it!!!
declare @MaxRowdateTime datetime
select @MaxRowdateTime = max(DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))) from [avaya].[dbo].[hSplit]
select
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))
,@MaxRowdatetime as hsplitMaxrowdatetime
, *
from [stg].[avaya_hSplit]
Where
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7))) > @MaxRowdatetime
December 3, 2014 at 8:53 pm
craig.bobchin (12/3/2014)
Thanks that worked. How can I concatenate this with a date field. neither '&' nor '+' seem to work.
Is the date "field" an Integer, as well? If it is, see the function in MSDB that I posted about in my previous post on this thread.
EDIT. Never mind. I see from you query that it's either a DATETIME or a VARCHAR that can easily be converted to a DATETIME.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2014 at 9:10 pm
craig.bobchin (12/3/2014)
Got it!!!declare @MaxRowdateTime datetime
select @MaxRowdateTime = max(DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))) from [avaya].[dbo].[hSplit]
select
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))
,@MaxRowdatetime as hsplitMaxrowdatetime
, *
from [stg].[avaya_hSplit]
Where
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7))) > @MaxRowdatetime
What is the data-type of the "row_date" column in the [stg].[avaya_hSplit] table?
Also, based on the schema and table name, it would appear that you might be importing from a file that was created by an Avaya telephone system. With that thought in mind, are you sure that the "starttime" column is an integer based time and not simply the number of seconds since midnight?
Also, how many rows are you working with at a time and why do you think you need to have a date/time format that can handle 100 nano second increments for a SELECT especially since most phone systems only handle seconds?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2014 at 9:45 pm
Jeff Moden (12/3/2014)
craig.bobchin (12/3/2014)
Got it!!!declare @MaxRowdateTime datetime
select @MaxRowdateTime = max(DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))) from [avaya].[dbo].[hSplit]
select
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))
,@MaxRowdatetime as hsplitMaxrowdatetime
, *
from [stg].[avaya_hSplit]
Where
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7))) > @MaxRowdatetime
What is the data-type of the "row_date" column in the [stg].[avaya_hSplit] table?
Also, based on the schema and table name, it would appear that you might be importing from a file that was created by an Avaya telephone system. With that thought in mind, are you sure that the "starttime" column is an integer based time and not simply the number of seconds since midnight?
Also, how many rows are you working with at a time and why do you think you need to have a date/time format that can handle 100 nano second increments for a SELECT especially since most phone systems only handle seconds?
If everything that I'm assuming is true and now that we know what you're really doing, we can greatly simplify the code and, perhaps, make it a little faster while we're at it (although, to be clear, I've not tested for performance because I don't have your data nor know the true nature of it).
DECLARE @MaxRowDateTime DATETIME;
SELECT @MaxRowDateTime = MAX(DATEADD(hh,starttime/100,DATEADD(mi,starttime%100,DATEDIFF(dd,0,row_date))))
FROM avaya.dbo.hSplit
;
WITH
cteConvert AS
(
SELECT StgDT = DATEADD(hh,starttime/100,DATEADD(mi,starttime%100,DATEDIFF(dd,0,row_date)))
FROM stg.avaya_hSplit
)
SELECT StgDT
,hsplitMaxrowdatetime = @MaxRowdateTime
FROM cteConvert
WHERE StgDT > @MaxRowdateTime
;
That being said, if this IS for an Avaya telephone system, then neither of our methods are actually any good even though they appear to work. I believe that if you have a long running phone call that starts before this run, it won't yet be available in the file that you've import or the staging table that's written to (whatever). If there are other calls that completed before the long call and before this run executed, the long running call will NEVER make it to this query. It will, instead, be filtered out by the WHERE clause.
You need to do an actual UPSERT instead of relying on a MAX. My hunch is that the "row_date" will also be the date the row was written and not the date that the call occurred, which could happen with a long running call that started before midnight and ended the next day after midnight, which is when it is actually written out to the file or table from the telephone system.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2014 at 10:07 am
SELECT time_int, CAST(DATEADD(MINUTE, time_int / 100 * 60 + time_int % 100, 0) AS time) AS time
FROM (
SELECT 0 AS time_int UNION ALL
SELECT 30 UNION ALL
SELECT 107 UNION ALL
SELECT 1723
) AS test_data
Edit: To add to an existing date/datetime, simply replace the "0" with that date/datetime and drop the CAST to time, of course. For example: DATEADD(MINUTE, time_int / 100 * 60 + time_int % 100, date_column).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 4, 2014 at 11:28 am
Jeff Moden (12/3/2014)
Jeff Moden (12/3/2014)
craig.bobchin (12/3/2014)
Got it!!!declare @MaxRowdateTime datetime
select @MaxRowdateTime = max(DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))) from [avaya].[dbo].[hSplit]
select
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7)))
,@MaxRowdatetime as hsplitMaxrowdatetime
, *
from [stg].[avaya_hSplit]
Where
DATEADD(day, DATEDIFF(day,'19000101',row_date), CAST(CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), starttime), 4), 3, 0, ':')) AS DATETIME2(7))) > @MaxRowdatetime
What is the data-type of the "row_date" column in the [stg].[avaya_hSplit] table?
Also, based on the schema and table name, it would appear that you might be importing from a file that was created by an Avaya telephone system. With that thought in mind, are you sure that the "starttime" column is an integer based time and not simply the number of seconds since midnight?
Also, how many rows are you working with at a time and why do you think you need to have a date/time format that can handle 100 nano second increments for a SELECT especially since most phone systems only handle seconds?
If everything that I'm assuming is true and now that we know what you're really doing, we can greatly simplify the code and, perhaps, make it a little faster while we're at it (although, to be clear, I've not tested for performance because I don't have your data nor know the true nature of it).
DECLARE @MaxRowDateTime DATETIME;
SELECT @MaxRowDateTime = MAX(DATEADD(hh,starttime/100,DATEADD(mi,starttime%100,DATEDIFF(dd,0,row_date))))
FROM avaya.dbo.hSplit
;
WITH
cteConvert AS
(
SELECT StgDT = DATEADD(hh,starttime/100,DATEADD(mi,starttime%100,DATEDIFF(dd,0,row_date)))
FROM stg.avaya_hSplit
)
SELECT StgDT
,hsplitMaxrowdatetime = @MaxRowdateTime
FROM cteConvert
WHERE StgDT > @MaxRowdateTime
;
That being said, if this IS for an Avaya telephone system, then neither of our methods are actually any good even though they appear to work. I believe that if you have a long running phone call that starts before this run, it won't yet be available in the file that you've import or the staging table that's written to (whatever). If there are other calls that completed before the long call and before this run executed, the long running call will NEVER make it to this query. It will, instead, be filtered out by the WHERE clause.
You need to do an actual UPSERT instead of relying on a MAX. My hunch is that the "row_date" will also be the date the row was written and not the date that the call occurred, which could happen with a long running call that started before midnight and ended the next day after midnight, which is when it is actually written out to the file or table from the telephone system.
Jeff,
This is for data coming from an Avaya phone system. We're importing data from a few tables from the Informix version of Avaya for our dashboards via SSIS. This set of queries are for hSplit, hAgent, haglog and Synonyms (an Upsert). What I'm looking to do here is pull in any new records from those tables since the last package run.
The package is to be run every 30 minutes (our interval). We'll be pulling call_rec data as well. As we're using ECHI that's coming from the ECH files on the FTP servers.
With these tables, an upsert isn't necessary since the records are not updated once they're written. A new record is appended to the tables in Avaya.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply