?
Solved

CONVERT DATETIME TO DATE

Posted on 2016-07-19
4
Medium Priority
?
125 Views
Last Modified: 2016-07-19
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'
0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41719892
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)
0
 

Author Comment

by:Karen Schaefer
ID: 41719895
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'
0
 

Author Closing Comment

by:Karen Schaefer
ID: 41719897
thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41719902
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
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question