October 1, 2012 at 1:42 am
HI Need help to get 2 things out of the below result set.
1) Make the StartDateTime as '2012-xx-xx 12:00:00.000' for the respective EndDateTime
2) And Runtime from 12 AM till the EndDateTime.
StartDateTime EndDateTime RunTime(Minutes)
2012-09-30 12:35:26.0332012-09-30 12:51:05.17015.650000
2012-09-29 16:27:35.9572012-09-29 16:43:45.98716.166666
2012-09-28 20:07:19.4932012-09-28 20:23:33.34316.233333
2012-09-27 01:05:07.5132012-09-27 01:20:00.41014.883333
2012-09-20 11:11:27.0832012-09-20 11:29:00.03717.550000
2012-09-19 17:56:50.7002012-09-19 18:15:17.54018.450000
October 1, 2012 at 1:52 am
The standard way of doing these casts is to go via varchar and back to datetime, truncating any data not required
e.g.
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) -- get date as text truncating any time
SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME)
-- So if you want 12pm (noon) - you would add that in the text phase
SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 120) + ' 12:00:00' AS DATETIME)
-- Note the space in the time: ' 12:00:00'
October 1, 2012 at 2:01 am
SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 120) + ' 12:00:00' AS DATETIME)
seems to working good but from the below result set, I need the StartDateTime to be on the same date as EndDateTime. But I'm getting same date for all records under startDateTime.
StartDateTime EndDateTime RunTime(Minutes)
2012-10-01 12:00:00.0002012-09-30 12:51:05.17015.650000
2012-10-01 12:00:00.0002012-09-29 16:43:45.98716.166666
2012-10-01 12:00:00.0002012-09-28 20:23:33.34316.233333
2012-10-01 12:00:00.0002012-09-27 01:20:00.41014.883333
2012-10-01 12:00:00.0002012-09-20 11:29:00.03717.550000
I need the result set to be as below
StartDateTime EndDateTime RunTime(Minutes)
2012-09-30 12:00:00.0002012-09-30 12:51:05.17015.650000
2012-09-29 12:00:00.0002012-09-29 16:43:45.98716.166666
2012-09-28 12:00:00.0002012-09-28 20:23:33.34316.233333
2012-09-27 12:00:00.0002012-09-27 01:20:00.41014.883333
2012-09-26 12:00:00.0002012-09-20 11:29:00.03717.550000
October 1, 2012 at 2:04 am
t.brown 89142 (10/1/2012)
The standard way of doing these casts is to go via varchar and back to datetime, truncating any data not requirede.g.
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) -- get date as text truncating any time
SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME)
-- So if you want 12pm (noon) - you would add that in the text phase
SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 120) + ' 12:00:00' AS DATETIME)
-- Note the space in the time: ' 12:00:00'
No.
If you cast to a VARCHAR then back, it's slow.
Either cast to a DATE, or use DATEADD e.g.: -
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, theDate)),
CAST(CAST(theDate AS DATE) AS DATETIME)
FROM (VALUES(GETDATE())) a(theDate);
Mac1986 (10/1/2012)
HI Need help to get 2 things out of the below result set.1) Make the StartDateTime as '2012-xx-xx 12:00:00.000' for the respective EndDateTime
2) And Runtime from 12 AM till the EndDateTime.
StartDateTime EndDateTime RunTime(Minutes)
2012-09-30 12:35:26.0332012-09-30 12:51:05.17015.650000
2012-09-29 16:27:35.9572012-09-29 16:43:45.98716.166666
2012-09-28 20:07:19.4932012-09-28 20:23:33.34316.233333
2012-09-27 01:05:07.5132012-09-27 01:20:00.41014.883333
2012-09-20 11:11:27.0832012-09-20 11:29:00.03717.550000
2012-09-19 17:56:50.7002012-09-19 18:15:17.54018.450000
In part 1, you talk about 12PM, then in part 2 you talk about 12AM. Which is it?
Here's your data set up so that people can use it: -
SELECT [StartDateTime], [EndDateTime], [RunTime(Minutes)]
INTO #sampleData
FROM (VALUES('2012-09-30 12:35:26.033','2012-09-30 12:51:05.170',15.650000),
('2012-09-29 16:27:35.957','2012-09-29 16:43:45.987',16.166666),
('2012-09-28 20:07:19.493','2012-09-28 20:23:33.343',16.233333),
('2012-09-27 01:05:07.513','2012-09-27 01:20:00.410',14.883333),
('2012-09-20 11:11:27.083','2012-09-20 11:29:00.037',17.550000),
('2012-09-19 17:56:50.700','2012-09-19 18:15:17.540',18.450000)
)a([StartDateTime], [EndDateTime], [RunTime(Minutes)]);
My guess is that you want something like this: -
SELECT [StartDateTime], [EndDateTime], [DiffInMilliSeconds]/60000.0 AS [RunTime(Minutes)]
FROM (SELECT CAST(CAST([EndDateTime] AS DATE) AS DATETIME), [EndDateTime],
DATEDIFF(MS,CAST(CAST([EndDateTime] AS DATE) AS DATETIME),[EndDateTime])
FROM #sampleData) a([StartDateTime], [EndDateTime], [DiffInMilliSeconds]);
October 1, 2012 at 2:06 am
GETDATE() is just a function that returns today's date, as an example datetime.
You would substitute your date column names
SELECT CAST(CONVERT(VARCHAR(10), StartDate, 120) + ' 12:00:00' AS DATETIME)
If you only need StartDates the same as endDate, then use a where condition
WHERE CONVERT(VARCHAR(10), startDate, 120) = CONVERT(Varchar(10), endDate, 120)
October 1, 2012 at 2:24 am
Thanks fro the assistance. I need the WorkQueueStartWorkDate to be starting at 12:00 AM with respect to the WorkQueueEndWorkDate. I'm using the below where clause but am I do not see any change in the result set.
and CONVERT(VARCHAR(10), WQ.WorkQueueStartWorkDate, 120) = CONVERT(Varchar(10), WQ.WorkQueueEndWorkDate, 120)
October 1, 2012 at 2:31 am
OK this is getting a little difficult to work out.
Just so we're working to the same definitions:
12AM is midnight SQL time '00:00:00' , 12PM is noon SQL time '12:00:00'
Also looking at your data start time and end time - the run time minutes does not seem to match the difference.
Perhaps you can post your query, table definitions and sample data.
October 1, 2012 at 2:32 am
Something like this?
DECLARE @Table TABLE (SD DATETIME, ED DATETIME)
INSERT INTO @Table VALUES
('2012-09-30 12:35:26.033','2012-09-30 12:51:05.170'),-- 15.650000
('2012-09-29 16:27:35.957','2012-09-29 16:43:45.987'),-- 16.166666
('2012-09-28 20:07:19.493','2012-09-28 20:23:33.343'),-- 16.233333
('2012-09-27 01:05:07.513','2012-09-27 01:20:00.410'),-- 14.883333
('2012-09-20 11:11:27.083','2012-09-20 11:29:00.037'),-- 17.550000
('2012-09-19 17:56:50.700','2012-09-19 18:15:17.540')-- 18.450000
SELECT * FROM @Table
SELECT SD = DATEADD(DAY, 0, DATEDIFF(DAY, 0, SD)), ED, [DIFFERENCE] = DATEDIFF(MINUTE,DATEADD(DAY, 0, DATEDIFF(DAY, 0, SD)), ED) FROM @Table
Results in
SD |ED |DIFFERENCE
2012-09-30 00:00:00.000 |2012-09-30 12:51:05.170 |771
2012-09-29 00:00:00.000 |2012-09-29 16:43:45.987 |1003
2012-09-28 00:00:00.000 | 2012-09-28 20:23:33.343 |1223
2012-09-27 00:00:00.000 |2012-09-27 01:20:00.410 |80
2012-09-20 00:00:00.000 |2012-09-20 11:29:00.037 |689
2012-09-19 00:00:00.000 |2012-09-19 18:15:17.540 |1095
October 1, 2012 at 2:49 am
Why not just STUFF it?
SELECT [StartDateTime], [EndDateTime], [RunTime(Minutes)]
INTO #sampleData
FROM (VALUES('2012-10-01 12:35:26.033','2012-09-30 12:51:05.170',15.650000),
('2012-10-01 16:27:35.957','2012-09-29 16:43:45.987',16.166666),
('2012-10-01 20:07:19.493','2012-09-28 20:23:33.343',16.233333),
('2012-10-01 01:05:07.513','2012-09-27 01:20:00.410',14.883333),
('2012-10-01 11:11:27.083','2012-09-20 11:29:00.037',17.550000),
('2012-10-01 17:56:50.700','2012-09-19 18:15:17.540',18.450000)
)a([StartDateTime], [EndDateTime], [RunTime(Minutes)]);
SELECT *
,NewStartDT=CAST(STUFF(
StartDatetime, 1, 23
, SUBSTRING(EndDateTime, 1, 10) + ' 12:00:00.000') AS DATETIME)
,NewStartDT2=DATEADD(day, DATEDIFF(day, 0, EndDateTime), 0)+0.5
FROM #sampleData
DROP TABLE #sampleData
Edit: I added a second way that's a bit more terse.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 1, 2012 at 4:27 am
Ok. Sorry for confusion. Lets start fresh here.. Below is the query that I wrote to get the 90 day processing times of one our jobs.
DECLARE @XHours INT = 2160
SELECT WQ.WorkQueueStartWorkDate as 'StartDateTime',WQ.WorkQueueEndWorkDate as 'EndDateTime',
cast((datediff (ss,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate)) as decimal(8,0))/3600.0 [RunTime(Hours)]
FROM CloudBIMetaData.dbo.WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
LEFT JOIN CloudBIMetaData.dbo.TransformationPackageInfaWorkflow TPIW WITH (NOLOCK)
ON TPIW.TransformationPackageID = TP.TransformationPackageID
WHERE WorkQueueStatusDate >= DATEADD(HH,-1*@XHours,GETDATE())
and TP.TransformationPackageName = 'DMPropagation_TSQLScript'
ORDER BY WQ.WorkQueueEndWorkDate DESC
Result Set:
StartDateTime EndDateTime RunTime(Hours)
2012-09-30 12:35:26.0332012-09-30 12:51:05.1700.260833
2012-09-29 16:27:35.9572012-09-29 16:43:45.9870.269444
2012-09-28 20:07:19.4932012-09-28 20:23:33.3430.270555
2012-09-27 01:05:07.5132012-09-27 01:20:00.4100.248055
2012-09-20 11:11:27.0832012-09-20 11:29:00.0370.292500
2012-09-19 17:56:50.7002012-09-19 18:15:17.5400.307500
........ 90 records for 90 days.
Now all I need is to change the StartDateTimeto 12:00 AM thats it. I need a result set a below.
StartDateTime EndDateTime RunTime(Hours)
2012-09-30 00:00:00.0002012-09-30 12:51:05.17012.51
2012-09-29 00:00:00.0002012-09-29 16:43:45.98716.43
2012-09-28 00:00:00.0002012-09-28 20:23:33.34320.2333
2012-09-27 00:00:00.0002012-09-27 01:20:00.41001.20
2012-09-20 00:00:00.0002012-09-20 11:29:00.03711.29
2012-09-19 00:00:00.0002012-09-19 18:15:17.54018.1517
........ 90 records for 90 days.
October 1, 2012 at 4:40 am
In that case, the startdate just becomes the end date truncated to midnight:
SELECT *
,NewStartDT=DATEADD(day, DATEDIFF(day, 0, EndDateTime), 0)
FROM #sampleData
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 1, 2012 at 4:58 am
That was real easy. Now all I need is to get the Run time form StartDateTime to EndDateTime in hours.
October 1, 2012 at 5:03 am
from the below Query, I need a 3rd column which would calculate Runtime(Hours) from 12:00 AM to WorkQueueEndWorkDate.
DECLARE @XHours INT = 2160
SELECT StartDateTime=DATEADD(day, DATEDIFF(day, 0, WQ.WorkQueueEndWorkDate), 0),WQ.WorkQueueEndWorkDate as 'EndDateTime'
FROM CloudBIMetaData..WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
LEFT JOIN CloudBIMetaData.dbo.TransformationPackageInfaWorkflow TPIW WITH (NOLOCK)
ON TPIW.TransformationPackageID = TP.TransformationPackageID
WHERE WQ.WorkQueueStatusDate >= DATEADD(HH,-1*@XHours,GETDATE())
and TP.TransformationPackageName = 'DMPropagation_TSQLScript'
ORDER BY WQ.WorkQueueEndWorkDate DESC
October 1, 2012 at 5:58 am
If you just need hours (from the truncated startdatetime):
DATEDIFF(hour, DATEADD(day, DATEDIFF(day, 0, EndDateTime), 0), EndDateTime)
If you need like HH:MM:SS, that's a bit more challenging but doable.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 1, 2012 at 7:16 am
Mac1986 (10/1/2012)
from the below Query, I need a 3rd column which would calculate Runtime(Hours) from 12:00 AM to WorkQueueEndWorkDate.DECLARE @XHours INT = 2160
SELECT StartDateTime=DATEADD(day, DATEDIFF(day, 0, WQ.WorkQueueEndWorkDate), 0),WQ.WorkQueueEndWorkDate as 'EndDateTime'
FROM CloudBIMetaData..WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
LEFT JOIN CloudBIMetaData.dbo.TransformationPackageInfaWorkflow TPIW WITH (NOLOCK)
ON TPIW.TransformationPackageID = TP.TransformationPackageID
WHERE WQ.WorkQueueStatusDate >= DATEADD(HH,-1*@XHours,GETDATE())
and TP.TransformationPackageName = 'DMPropagation_TSQLScript'
ORDER BY WQ.WorkQueueEndWorkDate DESC
Now, I don't know what the circumstances are for your choice of the NOLOCK hint. This may not be applicable to you and without more information there is no way for me to know.
However, in case you're not aware (and for future google searchers), please note that NOLOCK is not a good idea. I have some links for you to read through if you're interested : -
Allocation order scans with nolock
Consistency issues with nolock
Transient Corruption Errors in SQL Server error log caused by nolock
Dirty reads, read errors, reading rows twice and missing rows with nolock
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply