Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Date logic and Math inside an SQL Select Statement

Posted on 2005-03-28
Medium Priority
456 Views
I need some help wrapping my mind around this problem...

I have a table that stores the following fields:
TIMESTMP CHAR(22)     - Holds YYYYMMDDHHMMSS for the end time
ELAPTIME NUMBER        - Holds Execution time length in 100s of a second (16300 for 163 seconds)
ENDRUN CHAR(4)          - Holds HHMM for the end time
JOBNAME CHAR(64)      - Uniqu Identifier

I need to write a select that will pull:

Jobname, Start_Time, End_Time, Elapsed_Time like:

Job1, 20050328 07:00, 20050328 07:02, 2 Minutes

My database would have a record like:
TIMESTMP CHAR(22)     - 200503280702
ELAPTIME NUMBER        - 12000
ENDRUN CHAR(4)          - 0702
JOBNAME CHAR(64)      - Job1

For the life of me I can't get the math correct.  I need to somehow convert the timestamp or ENDRUN from HOURS-MINUTES to just seconds, then do the math, then convert back to HOURS-MINUTES OR I need to convert the TIMESTAMP into a date/time value and do math against it.

The Output then needs to be converted to a readable format like YYYY/MM/DD HH:MM

HELP!!
0
Question by:JRamos1200
• 10
• 7
• 6
• +1

LVL 70

Expert Comment

ID: 13647327
Still need to complete the Elapsted_Time calc, and the formats of the Start and End time, still working on that (unless someone else beats me to it :-) ):

SELECT JobName, DATEADD(MS, -[ElapTime Number] * 10, Timestmp) AS [Start_Time],
Timestmp AS [End_Time],
[Elaptime Number] / 6000 AS [Elapsed_Time]
FROM ...
WHERE ...
0

LVL 70

Expert Comment

ID: 13647383
SELECT JobName,
REPLACE(CONVERT(CHAR(16), DATEADD(MS, -[ElapTime Number] * 10, STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':')), 120), '-', '')  AS [Start_Time],
STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':') AS [End_Time],
CAST([Elaptime Number] / 6000 AS VARCHAR(2)) + ' Minutes' AS [Elapsed_Time]
FROM aTable

If Elapsed_Time can be more than minutes (hours, days?, etc.) please let me know.
0

LVL 70

Expert Comment

ID: 13647414
Need to shrink up that first column a bit :-) (actually a lot):

SELECT JobName,
CAST(REPLACE(CONVERT(CHAR(16), DATEADD(MS, -[ElapTime Number] * 10, STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':')), 120), '-', '') AS CHAR(15))  AS [Start_Time],
STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':') AS [End_Time],
CAST([Elaptime Number] / 6000 AS VARCHAR(2)) + ' Minutes' AS [Elapsed_Time]
FROM aTable
0

Author Comment

ID: 13647633
ELAPTIME will always contain a value in 100/ths of a second but the total number of seconds may indeed span multiple minutes/hous or days...

In your sample above, there seems to be several unknowns (STUFF and Elap Time Number) that I don't how to replace...  Can you explain it?

Thanks.
0

LVL 38

Expert Comment

ID: 13647638
Select JobName, Cast(TIMESTMP as DateTime) As Start_Time, ENDRUN as End_Time,
Datediff( ss, Cast(TIMESTMP as DateTime), Cast(Cast(Left(TIMESTMP,18) as Varchar) + cast( ENDRUN        as varchar) AS DateTime))/60 as Elapsed_Time
From .....
0

LVL 38

Expert Comment

ID: 13647655
Oh, that is assuming that the stat and end dates are on the same date.  Just a future thought -- You may want to consider using a full date time stamp on the end time.  Just in case the job starts at 23:58 and runs 7 minutes.  That would really mess with your calculations.
0

Author Comment

ID: 13647689
2 things...

One, I MUST accomidate the fact that it may run into the next day but I can NOT change the contents of the table as it is a product supplied database

Two, I tried your code jimpen and it failed...

Select JOBNAME, Cast(TIMESTMP as DateTime) As Start_Time, ENDRUN as End_Time,
Datediff( ss, Cast(TIMESTMP as DateTime), Cast(Cast(Left(TIMESTMP,18) as Varchar) + cast( ENDRUN        as varchar) AS DateTime))/60 as Elapsed_Time
From CMR_RUNINF

Returned:
Syntax error converting datetime from Charecter string
0

LVL 70

Expert Comment

ID: 13647716
>> In your sample above, there seems to be several unknowns (STUFF and Elap Time Number) that I don't how to replace...  Can you explain it? <<

Why do you need to replace the STUFF(s)?  They should work as is.

[ElapTime Number] is the column name from your initial post.

0

LVL 70

Expert Comment

ID: 13647779
create table atable (
timestmp char(22),
[elaptime number] int,
endrun char(4)
)
insert into atable
--                               34 hours (1 day, 10 hours) + 33 minutes + 47 secs
select '200503280702', 100 * 60 * 60 * 34 + (100 * 60 * 33) + (100 * 47), '0702'

SELECT --JobName,
CAST(REPLACE(CONVERT(CHAR(16), DATEADD(MS, -[ElapTime Number] * 10, STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':')), 120), '-', '') AS CHAR(15))  AS [Start_Time],
STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':') AS [End_Time],
CASE WHEN [Elaptime Number] >= 360000 * 24 THEN CAST([Elaptime Number] / (360000 * 24) AS VARCHAR(2)) + ' Day(s) ' ELSE '' END +
CASE WHEN [Elaptime Number] % 360000 * 24 >= 360000 THEN CAST([Elaptime Number] % (360000 * 24) / 360000 AS VARCHAR(2)) + ' Hour(s) ' ELSE '' END +
CAST([Elaptime Number] % 360000 / 6000 AS VARCHAR(2)) + ' Minute(s) '
AS [Elapsed_Time]
FROM aTable
0

LVL 70

Expert Comment

ID: 13647794
Note that the Start_Time is two days earlier than the End_Time because of the increased execution time.
0

LVL 38

Expert Comment

ID: 13648307
My bad.....I re-read the problem (probably the same reason I'm having problems passing my certs). Try this on.  I know it isn't pretty, but it seems to work.

select DATEADD ( ms , (ELAPTIME * -1)  ,
CAST(substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':00.000' AS DATETIME)  ) as start_time,
CAST(  substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':00.000' AS DATETIME) AS END_TIME,
cast( ELAPTIME/100 as decimal(7,2))  /60  Run_Minutes,  JOBNAME
FROM CMR_RUNINF

0

Author Comment

ID: 13648653
Scott, I ran yours:

SELECT JobName,
CAST(REPLACE(CONVERT(CHAR(16), DATEADD(MS, -[ElapTime Number] * 10, STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':')), 120), '-', '') AS CHAR(15))  AS [Start_Time],
STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':') AS [End_Time],
CASE WHEN [Elaptime Number] >= 360000 * 24 THEN CAST([Elaptime Number] / (360000 * 24) AS VARCHAR(2)) + ' Day(s) ' ELSE '' END +
CASE WHEN [Elaptime Number] % 360000 * 24 >= 360000 THEN CAST([Elaptime Number] % (360000 * 24) / 360000 AS VARCHAR(2)) + ' Hour(s) ' ELSE '' END +
CAST([Elaptime Number] % 360000 / 6000 AS VARCHAR(2)) + ' Minute(s) '
AS [Elapsed_Time]
FROM CMR_JOBDEF

and got the following error...
CAST(REPLACE(CONVERT(CHAR(16), DATEADD(MS, -[ElapTime Number] * 10, STUFF(STUFF(Timestmp, 9, 0, ' '), 12, 0, ':')), 120), '-', '') AS CHAR(15))  AS [Start_Time],
*
ERROR at line 2:
ORA-00936: missing expression

Did I do something wrong?
0

Author Comment

ID: 13648663
Jimpen,

I ran yours:
select DATEADD ( ms , (ELAPTIME * -1)  ,
CAST(substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':00.000' AS DATETIME)  ) as start_time,
CAST(  substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':00.000' AS DATETIME) AS END_TIME,
cast( ELAPTIME/100 as decimal(7,2))  /60  Run_Minutes,  JOBNAME
FROM CMR_RUNINF;

and got:
FROM CMR_RUNINF
*
ERROR at line 13:
ORA-00904: invalid column name

Stil missing something in translation...
0

Author Comment

ID: 13648684
Oops, I am working on this in both Oracle and MSSQL and I tedted both of those in Oracle by mistake.  Let me try again...

Jimpen, This time yours came back with the following results...

2/23/2005 1:22:47 PM 2/23/2005 1:23:00 PM 2.016666 HRDaily_13
2/22/2005 7:07:47 AM 2/22/2005 7:08:00 AM 2.016666 HRDaily_13
2/21/2005 7:04:47 AM 2/21/2005 7:05:00 AM 2.083333 HRDaily_13
2/23/2005 1:25:47 PM 2/23/2005 1:26:00 PM 2.016666 HRDaily_14
2/22/2005 7:11:47 AM 2/22/2005 7:12:00 AM 2.016666 HRDaily_14
2/21/2005 7:07:47 AM 2/21/2005 7:08:00 AM 2.016666 HRDaily_14
2/23/2005 1:28:48 PM 2/23/2005 1:29:00 PM 2.000000 HRDaily_15
2/22/2005 7:16:47 AM 2/22/2005 7:17:00 AM 2.050000 HRDaily_15
2/21/2005 7:10:47 AM 2/21/2005 7:11:00 AM 2.050000 HRDaily_15
2/23/2005 1:31:47 PM 2/23/2005 1:32:00 PM 2.016666 HRDaily_16
2/22/2005 7:20:47 AM 2/22/2005 7:21:00 AM 2.083333 HRDaily_16
2/21/2005 7:13:47 AM 2/21/2005 7:14:00 AM 2.033333 HRDaily_16
2/23/2005 1:16:48 PM 2/23/2005 1:17:00 PM 2.000000 HRDaily_17
2/21/2005 6:23:47 PM 2/21/2005 6:24:00 PM 2.066666 HRDaily_17
2/21/2005 6:26:47 PM 2/21/2005 6:27:00 PM 2.016666 HRDaily_18
2/21/2005 6:26:48 PM 2/21/2005 6:27:00 PM 2.000000 HRDaily_21
2/21/2005 6:29:48 PM 2/21/2005 6:30:00 PM 2.000000 HRDaily_22
2/23/2005 1:17:47 PM 2/23/2005 1:18:00 PM 2.016666 HRPay_1
2/22/2005 7:07:47 AM 2/22/2005 7:08:00 AM 2.016666 HRPay_1
2/21/2005 7:04:47 AM 2/21/2005 7:05:00 AM 2.083333 HRPay_1
2/22/2005 7:25:47 AM 2/22/2005 7:26:00 AM 2.016666 HRPay_10
2/21/2005 7:15:47 AM 2/21/2005 7:16:00 AM 2.016666 HRPay_10
2/23/2005 2:00:47 PM 2/23/2005 2:01:00 PM 2.016666 HRPay_11
2/22/2005 7:20:47 AM 2/22/2005 7:21:00 AM 2.066666 HRPay_11
2/21/2005 7:13:47 AM 2/21/2005 7:14:00 AM 2.016666 HRPay_11
2/23/2005 2:00:47 PM 2/23/2005 2:01:00 PM 2.083333 HRPay_12
2/22/2005 7:20:47 AM 2/22/2005 7:21:00 AM 2.066666 HRPay_12
2/21/2005 7:13:47 AM 2/21/2005 7:14:00 AM 2.033333 HRPay_12
2/23/2005 2:00:47 PM 2/23/2005 2:01:00 PM 2.050000 HRPay_13
2/22/2005 7:20:47 AM 2/22/2005 7:21:00 AM 2.066666 HRPay

Notice how they all seem to take 12 seconds, even when it reports at 2+ minutes...  MUCH closer though!!
0

Author Comment

ID: 13648707
Scott, Yours when run:
SELECT JOBNAME,
CAST(REPLACE(CONVERT(CHAR(16), DATEADD(MS, -[ELAPTIME] * 10, STUFF(STUFF(TIMESTMP, 9, 0, ' '), 12, 0, ':')), 120), '-', '') AS CHAR(15))  AS [Start_Time],
STUFF(STUFF(TIMESTMP, 9, 0, ' '), 12, 0, ':') AS [End_Time],
CASE WHEN [ELAPTIME] >= 360000 * 24 THEN CAST([ELAPTIME] / (360000 * 24) AS VARCHAR(2)) + ' Day(s) ' ELSE '' END +
CASE WHEN [ELAPTIME] % 360000 * 24 >= 360000 THEN CAST([ELAPTIME] % (360000 * 24) / 360000 AS VARCHAR(2)) + ' Hour(s) ' ELSE '' END +
CAST([ELAPTIME] % 360000 / 6000 AS VARCHAR(2)) + ' Minute(s) '
AS [Elapsed_Time]
FROM CMR_RUNINF

Gives me:
Syntax error converting datetime from character string.

Note, the column name is ELAPTIME, not ELAPTIME NUMBER.  It is of type Number.  Also, this database is case sensative so I had to change the columns to upper case.
0

LVL 9

Accepted Solution

sudheeshthegreat earned 2000 total points
ID: 13651528
i wonder why you have the definition of TIMESTMP CHAR(22) when it can store only 14 values for YYYYMMDDHHMMSS. Also, you have mentioned the value in the database to be '200503280702'. Guess it should be '20050328070200' as per the format you gave.

Here are a few queries:

This converts the TIMESTMP field to a datetime variable: '2005-03-28 07:02:00.000'
select convert(datetime,substring(TIMESTMP,1,4) + '-' + substring(TIMESTMP,5,2)
+ '-' + substring(TIMESTMP,7,2) + ' ' + substring(TIMESTMP,9,2) + ':'
+  substring(TIMESTMP,11,2)+ ':' +  substring(TIMESTMP,13,2)
,120)

and this gives you the start datetime (again datetime variable)
select dateadd(ss,-ELAPTIME/100,convert(datetime,substring(TIMESTMP,1,4) + '-' + substring(TIMESTMP,5,2)
+ '-' + substring(TIMESTMP,7,2) + ' ' + substring(TIMESTMP,9,2) + ':'
+  substring(TIMESTMP,11,2)+ ':' +  substring(TIMESTMP,13,2)
,120))

for elapsed time:
select ELAPTIME/100 + ' minutes'
0

LVL 38

Expert Comment

ID: 13651924
I think I caught the error - used your example "TIMESTMP CHAR(22)     - 200503280702" which didn't have the seconds in it. Try this.

select DATEADD ( ms , (ELAPTIME * -1)  ,
CAST(substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME)  ) as start_time,
CAST(  substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME) AS END_TIME,
cast( ELAPTIME/100 as decimal(7,2))  /60  Run_Minutes,  JOBNAME
FROM CMR_RUNINF
0

Author Comment

ID: 13652033
jimpen,

That didn't make a change.  The math is still off and only seems to be subtracting about 12 seconds...  Here are the results:

2/23/2005 1:23:22 PM    2/23/2005 1:23:35 PM    2.016666    HRDaily_13
2/22/2005 7:08:10 AM    2/22/2005 7:08:23 AM    2.016666    HRDaily_13
2/21/2005 7:05:17 AM    2/21/2005 7:05:30 AM    2.083333    HRDaily_13
2/23/2005 1:26:26 PM    2/23/2005 1:26:39 PM    2.016666    HRDaily_14
2/22/2005 7:12:40 AM    2/22/2005 7:12:53 AM    2.016666    HRDaily_14
2/21/2005 7:08:17 AM    2/21/2005 7:08:30 AM    2.016666    HRDaily_14
2/23/2005 1:29:24 PM    2/23/2005 1:29:36 PM    2.000000    HRDaily_15
0

LVL 38

Expert Comment

ID: 13652244
The ELAPTIME is milliseconds, correct? Maybe the DATEADD is choking on the milliseconds. Try one of these two solutions.

select DATEADD ( ms , (cast(ELAPTIME as int) * -1)  ,
CAST(substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME)  ) as start_time,
CAST(  substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME) AS END_TIME,
cast( ELAPTIME/100 as decimal(7,2))  /60  Run_Minutes,  JOBNAME
FROM CMR_RUNINF

--------------------------------------------------

select DATEADD ( ss , (cast(ELAPTIME/100 as int) * -1)  ,
CAST(substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME)  ) as start_time,
CAST(  substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME) AS END_TIME,
cast( ELAPTIME/100 as decimal(7,2))  /60  Run_Minutes,  JOBNAME
FROM CMR_RUNINF
0

Author Comment

ID: 13652284
Scott and Jimpen, I really appreciate the work you two put into this.  Jimpen, You went over board trying to get it to work and I want you to know I understand that.  You were SOOOO Close to getting the math part right too.

select 'testem' as 'DATA_CENTER', JOBNAME, MEMNAME, NODEID, dateadd(ss,-ELAPTIME/100,convert(datetime,substring(TIMESTMP,1,4) + '-' + substring(TIMESTMP,5,2) + '-' + substring(TIMESTMP,7,2) + ' ' + substring(TIMESTMP,9,2) + ':' +  substring(TIMESTMP,11,2)+ ':' +  substring(TIMESTMP,13,2),120)) as 'START',
convert(datetime,substring(TIMESTMP,1,4) + '-' + substring(TIMESTMP,5,2) + '-' + substring(TIMESTMP,7,2) + ' ' + substring(TIMESTMP,9,2) + ':' +  substring(TIMESTMP,11,2)+ ':' +  substring(TIMESTMP,13,2),120) as 'END', (ELAPTIME/100)/60 as 'ELAPSED'
from CMR_RUNINF

and got the results I needed.  Thanks!

0

Author Comment

ID: 13652299
Jimpen,

ARGH!!  Your second sollution fixed the math issue and now it works!

select DATEADD ( ss , (cast(ELAPTIME/100 as int) * -1)  ,
CAST(substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME)  ) as start_time,
CAST(  substring(TIMESTMP,5,2) + '-' +
substring(TIMESTMP,7,2) + '-' +
substring(TIMESTMP,1,4) + ' ' +
substring(TIMESTMP,9,2) + ':' +
substring(TIMESTMP,11,2) + ':' +
substring(TIMESTMP,13,2) + '.000' AS DATETIME) AS END_TIME,
cast( ELAPTIME/100 as decimal(7,2))  /60  Run_Minutes,  JOBNAME
FROM CMR_RUNINF

is getting what I wanted.
0

LVL 38

Expert Comment

ID: 13652408
Ah well.  :( Would have appreciated the points. It looks like Sudheesh read the question better than I did the first time.
0

Author Comment

ID: 13652668
Jimpen, Here is your chance!  I need another tweak to that SQL so I can pull records that are within a time period.

I opened a new question: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21368249.html

See if you can answer it first!  I used your SQL as it is easier for me to read.

0

LVL 70

Expert Comment

ID: 13652975
This is the SQL Server topic area, so I provided an answer for SQL Server.  If you wanted Oracle or MySQL code, another topic area would have been more appropriate.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then readingâ€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month13 days, 9 hours left to enroll