0

I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth column uses the VARCHAR data type.

/****** Generate Random Date For Each Row (Between 1900 - 2015) ******/
UPDATE CCCINC.dbo.Employees
SET DateOfBirth = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 41975), '1900-01-01');

Result:

Feb 28 1930 12:00AM

How do I remove the TIME portion from the result? Also, if I wanted to change the DATE format to YYYYMMDD how would I accomplish that?

4
  • 3
    WHY does the DateOfBirth column use VARCHAR and not DATE? Do you also store numbers as strings and try to stuff last names into date columns? Aug 10, 2015 at 16:11
  • @AaronBertrand That was just how the programmer designed it. Agree that it is not optimal but that's what I'm working with.
    – NOSDUH
    Aug 10, 2015 at 16:15
  • So the developer chose the wrong data type and you don't have the ability to fix it using ALTER TABLE? Aug 10, 2015 at 16:16
  • @ypercube that still produces a time component (it's just midnight). Aug 10, 2015 at 16:19

1 Answer 1

3

Well, assuming that someone is holding a gun to your head, forcing you to not change the data type to something more sensible and logical:

UPDATE CCCINC.dbo.Employees
SET DateOfBirth = CONVERT(CHAR(8), DATEADD(DAY, ABS(CHECKSUM(NEWID()) 
  % 41975), '1900-01-01'), 112);

Strong, strongly, strongly suggest you change the data type of that column to use the right data type (DATE). Then you can just use your original query and not worry about the "format" at all. Format is what you worry about when you present the data, not when you store it.

1
  • Comes with the territory of QA'ing the new programmer's application. I already submitted a request to change the data type but until that fix is pushed to deployment I had to find a work around. Thank you for the help.
    – NOSDUH
    Aug 10, 2015 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.