• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:


How do I modify the createdDate (datetime) to just a date?

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'
Karen Schaefer
Karen Schaefer
  • 2
1 Solution
I would recommend NOT converting the column in the where clause.
That would mean you would need to apply the conversion to every row in order to evaluate the condition.

Instead, convert your dates to datetimes,  or because sql server allows comparison of those types, then simply compare and don't do any conversion.

your current conditions may fail because June only has 30 days,  so '2016-06-31'  will result in an error.

if you want to make it explicit that you're comparing datetime values then you can CAST the dates as datetimes

select * from your_table
 where CreatedDate >= cast('2016-06-01' as datetime)  AND CreatedDate <= cast('2016-06-30' as datetime)
Karen SchaeferAuthor Commented:
thanks changed criteria to include the time:

where CreatedDate>= '2016-07-01 00:00:00.000'AND CreatedDate<= '2016-07-30 00:00:00.000'
Karen SchaeferAuthor Commented:
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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now