Sql-server – How to drop the TIME portion from DATEADD

datedate formatdatetimesql serverupdate

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?

Best Answer

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.

SUGGESTION #1

You could use the MONTH() function

mysql> select MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            3 |
+--------------+
1 row in set (0.03 sec)

mysql> select MONTH('2013-03-17 02:53:47');
+------------------------------+
| MONTH('2013-03-17 02:53:47') |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.04 sec)

mysql>

You would have to put the MONTH function into the SQL command you are running.

SUGGESTION #2

You could also try the PHP Date Function

$month = date("m",$date)

Also DATE_FORMAT(date_column, '%c') to get '3' as result and DATE_FORMAT(date_column, '%m') to get '03' as result.

Sql-server – Extract time portion of Datetime2(7) in hhmmssfff format

Please don't use varchar without length.
Please use semi-colons as statement terminators.

I don't think there is a more covert way to do this. Here is how I would do it (14 characters shorter, ignoring the semi-colon):

DECLARE @mdate AS DATETIME2(7);

SET @mdate = '2012-03-15 10:13:27.5437431';

SELECT REPLACE(REPLACE(CONVERT(TIME(3),@mdate),'.',''),':','');

Best Answer

Related Solutions

Mysql – How to get only month from MySQL date/time

SUGGESTION #1

SUGGESTION #2

Sql-server – Extract time portion of Datetime2(7) in hhmmssfff format

Related Question