mardi 2 août 2016

set hour, minute and seconds to exact numbers in sql

I am populating my databae with dummy information for testing purposes, thanks to http://ift.tt/2arPDUZ And it is working, however my date is randomized and get random years/months but allways the current time, a few examples exctracted from my database:

2017-05-30 16:41:53.190
2017-07-21 16:41:53.193
2016-04-23 16:41:53.193

however my application using these dates must have them in the form of

2017-05-30 16:00:00.000
2017-07-21 01:30:00.000
2016-04-23 04:45:00.000

In other words the date is currently fine, but the minute should be on of 00,15,30,45 and seconds and miliseconds should allways be 00.

Any way to add random times on the format above to my sql?

My current SQL logic is as follows:

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME
DECLAre @LoopThisManyTimes INT

SET @Lower = -360 /* move 360 days backwards */
SET @Upper = 360 /*  move 360 days forward */
SET @RowCount = 0 /* start the counter at 0 */
SET @LoopThisManyTimes = 10 /* set the number of posts to add */

USE [Scheduler]
TRUNCATE TABLE [dbo].[Appointments] /* clear table for testing purposes */
WHILE @RowCount < @LoopThisManyTimes /*loop and add to table */
BEGIN
SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SET @InsertDate = DATEADD(dd, @Random, GETDATE())




Aucun commentaire:

Enregistrer un commentaire