Karen Schaefer
asked on
CONVERT DATETIME TO DATE
How do I modify the createdDate (datetime) to just a date?
CreatedDate
2016-07-19 16:00:19.710
The also use it in the Where clause:--WHERE CreatedDate >='2016-06-01' AND CreatedDate <= '2016-06-31'
CreatedDate
2016-07-19 16:00:19.710
The also use it in the Where clause:--WHERE CreatedDate >='2016-06-01' AND CreatedDate <= '2016-06-31'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
it isn't necessary to include the time, but if you do I also suggest you include the letter T which make the string into a "safe format" that SQL Server will always interpret correctly regardless of database settings.
where CreatedDate>= '2016-07-01T00:00:00.000' AND CreatedDate<= '2016-07-30T00:00:00.000'
it is much simpler to use YYYYMMDD which is perfectly safe also (MS SQL will always interpret it as YYYYMMDD)
where CreatedDate>= '20160701' AND CreatedDate<= '20160730'
and the time is assumed to be at 00:00:00+00000000
HOWEVER
Your date range is 24 hours short of a full month
If you change to using >= with < like this:
where CreatedDate>= '20160701' AND CreatedDate< '20160801' --<< less than 1st of next month
now you get exactly one month
where CreatedDate>= '2016-07-01T00:00:00.000' AND CreatedDate<= '2016-07-30T00:00:00.000'
it is much simpler to use YYYYMMDD which is perfectly safe also (MS SQL will always interpret it as YYYYMMDD)
where CreatedDate>= '20160701' AND CreatedDate<= '20160730'
and the time is assumed to be at 00:00:00+00000000
HOWEVER
Your date range is 24 hours short of a full month
If you change to using >= with < like this:
where CreatedDate>= '20160701' AND CreatedDate< '20160801' --<< less than 1st of next month
now you get exactly one month
ASKER
where CreatedDate>= '2016-07-01 00:00:00.000'AND CreatedDate<= '2016-07-30 00:00:00.000'