October 11, 2013 at 8:36 am
Hi -
What is a best way to set a field value to today date and time would be a always set to 4:00:00? Please advice
October 11, 2013 at 8:46 am
This should do it.
select dateadd(dd, datediff(dd, 0, GETDATE()), 0) + CAST('04:00:00' as time)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2013 at 8:48 am
lsalih (10/11/2013)
Hi -What is a best way to set a field value to today date and time would be a always set to 4:00:00? Please advice
Loads of ways. What have you tried? Here's a couple off the top of my head.
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) AS [Option 1],
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', GETDATE()), '2000-01-01 04:00:00') AS [Option 2];
October 11, 2013 at 8:48 am
Sean -
Many many thanks to you, it worked.
October 11, 2013 at 8:51 am
Yes, both ways worked... Once again, thank you all for your input..
October 11, 2013 at 8:56 am
This should do it:
select TodayAt4am = dateadd(dd,datediff(dd,0,getdate()),'04:00:00')
Results:
TodayAt4am
-----------------------
2013-10-11 04:00:00.000
October 11, 2013 at 8:58 am
Michael -
Many thanks to you, I appreciate it... I got it.
October 11, 2013 at 9:01 am
I know it is irrelevant, as you're not going to be doing this over a lot of rows of data but in the interest of completeness, here's a performance comparison over 1 million rows: -
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
PRINT REPLICATE('=',80);
PRINT 'PERFORMANCE COMPARISON';
PRINT REPLICATE('=',80);
DECLARE @Loop CHAR(1) = '0', @HOLDER1 INT, @HOLDER2 DATETIME, @Duration CHAR(12), @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @HOLDER1 = COUNT(*)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('BaseLine Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
WHILE @Loop <= 5
BEGIN;
RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST(''04:00:00'' AS TIME) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST(''04:00:00'' AS TIME) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00')
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(dd, DATEDIFF(dd, ''2000-01-01 00:00:00'', randomDateTime), ''2000-01-01 04:00:00'') Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00')
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(DD,DATEDIFF(DD,0,randomDateTime),''04:00:00'') Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
SET @Loop = @Loop + 1;
END;
GO
================================================================================
PERFORMANCE COMPARISON
================================================================================
BaseLine Duration: 00:00:00:083
============
============
Loop: 0
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:390
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:290
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:323
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:347
============
Loop: 1
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:253
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:230
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:353
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:257
============
Loop: 2
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:290
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:253
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:363
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:267
============
Loop: 3
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:330
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:220
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:327
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:250
============
Loop: 4
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:267
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:263
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:337
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:270
============
Loop: 5
============
============
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:260
============
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:243
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:320
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:357
============
October 11, 2013 at 11:16 am
I appreciate your input, thanks for your time.
October 13, 2013 at 6:38 pm
Not that it means much until you upgrade, but you may want to consider avoiding these 2 because they seem to fail in SQL 2012 using Cadavre's test harness:
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + CAST('04:00:00' AS TIME)
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME)
The message I'm getting is:
Msg 402, Level 16, State 1, Line 40
The data types datetime and time are incompatible in the add operator.
Msg 402, Level 16, State 1, Line 53
The data types datetime and time are incompatible in the add operator.
Another option:
SELECT DATEADD(hour, 4, DATEDIFF(day, 0, GETDATE()))
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 14, 2013 at 2:09 am
dwain.c (10/13/2013)
Not that it means much until you upgrade, but you may want to consider avoiding these 2 because they seem to fail in SQL 2012 using Cadavre's test harness:
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + CAST('04:00:00' AS TIME)
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME)
The message I'm getting is:
Msg 402, Level 16, State 1, Line 40
The data types datetime and time are incompatible in the add operator.
Msg 402, Level 16, State 1, Line 53
The data types datetime and time are incompatible in the add operator.
That is very interesting, I'll have to remember to test it for confirmation when I get home to my 2012 instance.
dwain.c (10/13/2013)
Another option:
SELECT DATEADD(hour, 4, DATEDIFF(day, 0, GETDATE()))
I'm sure you already did it, but I was curious (I've also included "SELECT DATEADD(hour, 4,CAST(CAST(GETDATE() AS DATE) AS DATETIME))" as I've found that to be faster than the traditional DATEADD DATEDIFF methods): -
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
PRINT REPLICATE('=',80);
PRINT 'PERFORMANCE COMPARISON';
PRINT REPLICATE('=',80);
DECLARE @Loop CHAR(1) = '0', @HOLDER1 INT, @HOLDER2 DATETIME, @Duration CHAR(12), @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @HOLDER1 = COUNT(*)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('BaseLine Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
WHILE @Loop <= 5
BEGIN;
RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('WARNING! May not work in SQL Server 2012',0,1) WITH NOWAIT;
RAISERROR('DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST(''04:00:00'' AS TIME) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('WARNING! May not work in SQL Server 2012',0,1) WITH NOWAIT;
RAISERROR('CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST(''04:00:00'' AS TIME) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00')
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(dd, DATEDIFF(dd, ''2000-01-01 00:00:00'', randomDateTime), ''2000-01-01 04:00:00'') Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00')
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(DD,DATEDIFF(DD,0,randomDateTime),''04:00:00'') Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime))
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER2 = DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME))
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
SET @Loop = @Loop + 1;
END;
GO
================================================================================
PERFORMANCE COMPARISON
================================================================================
BaseLine Duration: 00:00:00:090
============
============
Loop: 0
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:517
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:293
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:357
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:230
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:253
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:240
============
Loop: 1
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:250
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:213
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:303
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:220
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:270
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:247
============
Loop: 2
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:250
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:220
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:327
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:227
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:250
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:227
============
Loop: 3
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:247
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:210
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:320
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:243
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:250
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:217
============
Loop: 4
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:253
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:220
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:317
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:240
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:283
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:397
============
Loop: 5
============
============
WARNING! May not work in SQL Server 2012
DATEADD(dd, DATEDIFF(dd, 0, randomDateTime), 0) + CAST('04:00:00' AS TIME) Duration: 00:00:00:307
============
WARNING! May not work in SQL Server 2012
CAST(CAST(randomDateTime AS DATE) AS DATETIME)+CAST('04:00:00' AS TIME) Duration: 00:00:00:243
============
DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', randomDateTime), '2000-01-01 04:00:00') Duration: 00:00:00:353
============
DATEADD(DD,DATEDIFF(DD,0,randomDateTime),'04:00:00') Duration: 00:00:00:250
============
DATEADD(hour, 4, DATEDIFF(day, 0, randomDateTime)) Duration: 00:00:00:257
============
DATEADD(hour, 4,CAST(CAST(randomDateTime AS DATE) AS DATETIME)) Duration: 00:00:00:233
============
October 14, 2013 at 2:21 am
Cadavre (10/14/2013)
I'm sure you already did it
Hehe... Yes I did actually. I didn't post my results because I'd call all of the results overall too close to call so I only threw it out there because I think the choice here ends up being one you'd make on readability rather than raw performance.
Or maybe not... depends on what your biggest priority is.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply