How to get random datetime of current day in SQL Server I am working on a SQL query where I need to get datetime of any specific date.
For this I used the following query:

DECLARE @RandomDate datetime
DECLARE @fromDate datetime = '2018-04-07'

SELECT 
    @RandomDate = (DATEADD(day, ROUND(DATEDIFF(day, @fromDate, @fromDate) 
* RAND(CHECKSUM(NEWID())), 5), DATEADD(second, CHECKSUM(NEWID()) % 24000, @fromDate))) 

SELECT @RandomDate 


Here in the above example, I want to get datetime of date '2018-04-07' i.e 7 April. But sometime it is showing datetime of 6 april also. How to solve it?
The only way to go 'back-in-time' would be to add a negative number.  Try adding ABS to your query.

DECLARE @RandomDate datetime

DECLARE @fromDate datetime='2018-04-07'


SELECT @RandomDate= (DATEADD(day, ROUND(DATEDIFF(day, @fromDate, @fromDate) 
* RAND(CHECKSUM(NEWID())), 5),DATEADD(second, abs(CHECKSUM(NEWID())) % 86400, 
@fromDate))) 

SELECT  @RandomDate 


ABS - A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)