Creating Random Dates from Numbers in MySQL.

/*CREATING RANDOM DATES FROM NUMERICAL VALUES*/

create database if not exists naren;
use naren;
DROP TABLE IntToDateDemo;
DROP TABLE DATES;

create table if not exists IntToDateDemo
(
Number int
);

INSERT INTO INTTODATEDEMO VALUES
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000),
(ROUND(RAND()*25000000,0)+1400000000);

/*Creating Another Table with random dates value from existing table*/
CREATE TABLE Dates AS
SELECT NUMBER, from_unixtime(Number,'%Y-%m-%d') as DateDemo from IntToDateDemo;

UPDATE dates SET datedemo =  from_unixtime(ROUND(RAND()*25000000,0)+1400000000,'%Y-%m-%d');

/*Modifying column data type from character to date*/
ALTER TABLE DATES MODIFY COLUMN DATEDEMO DATE;

DROP TABLE IntToDateDemo;
SELECT * FROM Dates;



Click here to download .sql file


No comments

Post your comments

Powered by Blogger.