{"id":35539,"date":"2021-08-30T18:40:46","date_gmt":"2021-08-30T18:40:46","guid":{"rendered":"https:\/\/ittutorial.org\/?p=35539"},"modified":"2021-08-30T18:40:46","modified_gmt":"2021-08-30T18:40:46","slug":"random-value-for-datetime-column","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/random-value-for-datetime-column\/","title":{"rendered":"Random value for DATETIME column"},"content":{"rendered":"<p>Today, I am going to describe how to generate random value for DATETIME field within a given range. This is very useful specially for generating test data. For this, we will use couple of built-in functions like:<\/p>\n<ul>\n<li>DATEDIFF<\/li>\n<li>DATEADD<\/li>\n<li>RAND<\/li>\n<li>ROUND<\/li>\n<\/ul>\n<p><span style=\"text-decoration: underline\"><strong>Random DATETIME Value<\/strong><\/span><\/p>\n<p><code>DECLARE @startDate DATETIME -- start date<br \/>\nDECLARE @endDate DATETIME -- end date<br \/>\nDECLARE @noOfSec INT -- variable<br \/>\nDECLARE @randomSec INT -- variable<\/code><\/p>\n<p><code>SET @startDate = '2021-06-27 08:00 AM' -- assigning starting date<br \/>\nSET @endDate = '2021-06-27 08:30 AM' -- assigning end date<\/code><\/p>\n<p><code>-- assigning end date -- Get the number of seconds within the date range<br \/>\nset @noOfSec = DATEDIFF(SECOND, @startDate, @endDate)<\/code><\/p>\n<p><code>-- Get random seconds within the date range<br \/>\nset @randomSec = ROUND(((@noOfSec-1) * RAND()), 0)<\/code><\/p>\n<p><code>-- Add the random seconds to get the random datetime value within the daterange<br \/>\nSELECT DATEADD(SECOND, @randomSec, @startDate)<\/code><\/p>\n<p>Hope this will be useful for you. Happy TSQLing!<\/p>\n<p>This is first published <a href=\"https:\/\/www.techearth.xyz\/post\/2021\/06\/28\/random-value-for-int-and-datetime-column\" target=\"_blank\" rel=\"noopener\">here<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today, I am going to describe how to generate random value for DATETIME field within a given range. This is very useful specially for generating test data. For this, we will use couple of built-in functions like: DATEDIFF DATEADD RAND ROUND Random DATETIME Value DECLARE @startDate DATETIME &#8212; start date DECLARE @endDate DATETIME &#8212; end &hellip;<\/p>\n","protected":false},"author":10927,"featured_media":35540,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3826],"tags":[],"class_list":["post-35539","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-tutorials"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2021\/08\/Random-Datetime.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/35539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10927"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=35539"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/35539\/revisions"}],"predecessor-version":[{"id":35541,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/35539\/revisions\/35541"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/35540"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=35539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=35539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=35539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}