Concatenate Random Date + Random Time = Error!

Question

Concatenate Random Date + Random Time = Error!

alex.sqldba

SSChampion

Points: 10254
More actions
October 20, 2015 at 2:48 am

#312432

Morning All,
This is driving me up the wall!
I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:
select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate,
CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTime
But when I try to make it a single datetime field:
select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)
Which returns with: Conversion failed when converting date and/or time from character string.
So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...
As always, your thoughts and help are appreciated
Cheers
Alex

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Here's one way:

select DateCols.*, theDatetime = cast(concat(cast(DateCols.theDate as char(10)), ' ', cast(DateCols.theTime as char(12))) as datetime)

from (select cast(cast(abs(checksum(newid())) % (780) + (33968) as datetime) as date) as theDate

,cast(cast(dateadd(millisecond, abs(checksum(newid())) % 86400000, '00:00') as time) as varchar(50)) as theTime

) DateCols;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Another way

SELECT

CONVERT(DATETIME,

CONVERT(VARCHAR(10),

CONVERT(DATETIME,

ABS(CHECKSUM(NEWID()))%(780)+(33968)

)

,120)

+'T'+

CONVERT(VARCHAR(12),

CONVERT(TIME,

DATEADD(MILLISECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00')

)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Here's another way, a million random-ish datetimes between 1947 and today:

-- A million datetimes between 1947 and today:

;WITH

t1 (x) AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (x)),

t2 (x) AS (SELECT 0 FROM t1, t1 tn),

t4 (x) AS (SELECT 0 FROM t2, t2 tn),

aMillionDates (MyDate) AS (SELECT DATEADD(SECOND,0-ABS(CHECKSUM(NEWID())),GETDATE()) FROM t4, t2)

SELECT YEAR(MyDate), COUNT(*)

FROM aMillionDates

GROUP BY YEAR(MyDate)

ORDER BY YEAR(MyDate)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Good resource can be found here by Jeff Moden

http://www.sqlservercentral.com/articles/Test+Data/88964/

DECLARE @NumberOfRows INT,

@StartDate DATETIME,

@EndDate DATETIME,

@Days INT

;

SELECT @NumberOfRows = 100,

@StartDate = '2015', --Inclusive

@EndDate = '2016', --Exclusive

@Days = DATEDIFF(dd,@StartDate,@EndDate)

;

SELECT TOP (@NumberOfRows)

SomeRandomDateTime = RAND(CHECKSUM(NEWID())) * @Days + @StartDate

--INTO #SomeTestTable

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 5

select dateadd(s,-1*rand()*10000000 , getdate())

Jayanth Kurup[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jayanth_Kurup (10/20/2015)
select dateadd(s,-1*rand()*10000000 , getdate())

How would you scale this up to a million rows?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

It doesnt 🙂 i figured the random data is generated row by row and not from a set

Jayanth Kurup[/url]

alex.sqldba SSChampion Points: 10254 More actions · Answer 8

Thanks all for your help! I've now got plenty of random test data and test dates!

Cheers

Alex

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Jayanth_Kurup (10/20/2015)
It doesnt 🙂 i figured the random data is generated row by row and not from a set

Even then, the set based method will work for a single row. I hope no newbie makes the mistake of using the single row method where they shouldn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 10

One can always hope 🙂

I wonder if we should test OLTP performance using data sets instead of actual row by row operations that will be encountered in production.

Its one of the reasons I shifted to using OSTRESS to try and simulate more real world use cases.

Jayanth Kurup[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Jayanth_Kurup (10/22/2015)
One can always hope 🙂
I wonder if we should test OLTP performance using data sets instead of actual row by row operations that will be encountered in production.
Its one of the reasons I shifted to using OSTRESS to try and simulate more real world use cases.

Yes... we should absolutely do that so that you don't continue justifying RBAR. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)