Need assistance to CAST the StartDate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need assistance to CAST the StartDate

18 posts, Page 1 of 2

1 2 »»

Need assistance to CAST the StartDate

Rate Topic

Display Mode

Topic Options

Author

Message

Mac1986

Posted Monday, October 01, 2012 1:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 180, Visits: 513

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.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

Post #1366348

t.brown 89142

Posted Monday, October 01, 2012 1:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140

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'

Post #1366359

Mac1986

Posted Monday, October 01, 2012 2:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 180, Visits: 513

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.000 2012-09-30 12:51:05.170 15.650000
2012-10-01 12:00:00.000 2012-09-29 16:43:45.987 16.166666
2012-10-01 12:00:00.000 2012-09-28 20:23:33.343 16.233333
2012-10-01 12:00:00.000 2012-09-27 01:20:00.410 14.883333
2012-10-01 12:00:00.000 2012-09-20 11:29:00.037 17.550000

I need the result set to be as below

StartDateTime EndDateTime RunTime(Minutes)
2012-09-30 12:00:00.000 2012-09-30 12:51:05.170 15.650000
2012-09-29 12:00:00.000 2012-09-29 16:43:45.987 16.166666
2012-09-28 12:00:00.000 2012-09-28 20:23:33.343 16.233333
2012-09-27 12:00:00.000 2012-09-27 01:20:00.410 14.883333
2012-09-26 12:00:00.000 2012-09-20 11:29:00.037 17.550000

Post #1366366

Cadavre

Posted Monday, October 01, 2012 2:04 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

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 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'

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)

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]);

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) 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

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1366371

t.brown 89142

Posted Monday, October 01, 2012 2:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140

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)

Post #1366374

Mac1986

Posted Monday, October 01, 2012 2:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 180, Visits: 513

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)

Post #1366386

t.brown 89142

Posted Monday, October 01, 2012 2:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140

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.

Post #1366390

anthony.green

Posted Monday, October 01, 2012 2:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366391

dwain.c

Posted Monday, October 01, 2012 2:49 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1366397

Mac1986

Posted Monday, October 01, 2012 4:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:28 PM
Points: 180, Visits: 513

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.033 2012-09-30 12:51:05.170 0.260833
2012-09-29 16:27:35.957 2012-09-29 16:43:45.987 0.269444
2012-09-28 20:07:19.493 2012-09-28 20:23:33.343 0.270555
2012-09-27 01:05:07.513 2012-09-27 01:20:00.410 0.248055
2012-09-20 11:11:27.083 2012-09-20 11:29:00.037 0.292500
2012-09-19 17:56:50.700 2012-09-19 18:15:17.540 0.307500
........ 90 records for 90 days.

Now all I need is to change the StartDateTime to 12:00 AM thats it. I need a result set a below.

StartDateTime EndDateTime RunTime(Hours)
2012-09-30 00:00:00.000 2012-09-30 12:51:05.170 12.51
2012-09-29 00:00:00.000 2012-09-29 16:43:45.987 16.43
2012-09-28 00:00:00.000 2012-09-28 20:23:33.343 20.2333
2012-09-27 00:00:00.000 2012-09-27 01:20:00.410 01.20
2012-09-20 00:00:00.000 2012-09-20 11:29:00.037 11.29
2012-09-19 00:00:00.000 2012-09-19 18:15:17.540 18.1517
........ 90 records for 90 days.

Post #1366466

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions