Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

set date/time to today date but certain time Expand / Collapse
Author
Message
Posted Friday, October 11, 2013 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:47 PM
Points: 104, Visits: 618
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



Post #1504040
Posted Friday, October 11, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,273, Visits: 12,105
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1504044
Posted Friday, October 11, 2013 8:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
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];




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" )!
Post #1504045
Posted Friday, October 11, 2013 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:47 PM
Points: 104, Visits: 618
Sean -

Many many thanks to you, it worked.



Post #1504046
Posted Friday, October 11, 2013 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:47 PM
Points: 104, Visits: 618
Yes, both ways worked... Once again, thank you all for your input..


Post #1504048
Posted Friday, October 11, 2013 8:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 3,135, Visits: 11,475
This should do it:
select TodayAt4am = dateadd(dd,datediff(dd,0,getdate()),'04:00:00')

Results:
TodayAt4am
-----------------------
2013-10-11 04:00:00.000


Post #1504051
Posted Friday, October 11, 2013 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:47 PM
Points: 104, Visits: 618
Michael -

Many thanks to you, I appreciate it... I got it.



Post #1504053
Posted Friday, October 11, 2013 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
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
============



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" )!
Post #1504056
Posted Friday, October 11, 2013 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 5:47 PM
Points: 104, Visits: 618
I appreciate your input, thanks for your time.


Post #1504104
Posted Sunday, October 13, 2013 6:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1504301
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse