How to Add a Date randomly

Question

How to Add a Date randomly

niladri.primalink

SSCommitted

Points: 1751
More actions
July 4, 2013 at 3:25 am

#279484

Hi All,
In a table (sale.person) I have more than 9 lacs row. I have only three columns. I need to add a column by Orderdate (which I have done by Alter table). Next I want to fill up the rows with date which should be set as a Order.
For example if the date of first row is 2013-07-04 then the date of the next row will be 2013-07-03 and so one.
Is there any way so that I can do it easily?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Simply add an IDENTITY column to your table (if you haven't one already).

Give each row the same value for OrderDate: 2013-07-04. Then simply distract the identity value from the OrderDate to get the required values for your OrderDate column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

niladri.primalink (7/4/2013)
Hi All,
In a table (sale.person) I have more than 9 lacs row. I have only three columns. I need to add a column by Orderdate (which I have done by Alter table). Next I want to fill up the rows with date which should be set as a Order.
For example if the date of first row is 2013-07-04 then the date of the next row will be 2013-07-03 and so one.
Is there any way so that I can do it easily?

Considering that there have been only 7.35 lac days since the year "0000", I have to ask why you want to do this. Is it a test table that you're trying to populate? If so, please see the following...

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

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

What in Buddha's name is a "lac day?"

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

niladri.primalink SSCommitted Points: 1751 More actions · Answer 4

Hi All,

Thanks for your response....

Yes I have run a loop like below

declare @i int=900000
declare @mydate date=getdate()
while @i>0
begin
set @mydate= dateadd(day,-1,@mydate)
print @mydate
set @i=@i-1
end

However it is not working as the last date it shows like 01-01-01....What I have done is that I have given first 10,000 a single date and then next 10,000 another date and so on....

However your input has helped me to solve it.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

dwain.c (7/4/2013)
What in Buddha's name is a "lac day?"

"lac" is 100,000 in the OP's language.

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

niladri.primalink (7/4/2013)
However your input has helped me to solve it.

Two way street here... please post the code you ended up with so someone else can learn from it. Thanks.

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

Jeff Moden (7/5/2013)
dwain.c (7/4/2013)
What in Buddha's name is a "lac day?"
"lac" is 100,000 in the OP's language.

Oh, I get it! It can be used like a productivity measure:

SLOC/lac-days

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

dwain.c (7/6/2013)
Jeff Moden (7/5/2013)
dwain.c (7/4/2013)
What in Buddha's name is a "lac day?"
"lac" is 100,000 in the OP's language.
Oh, I get it! It can be used like a productivity measure:
SLOC/lac-days

😛

--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)