Solved

CONVERT DATETIME TO DATE

Posted on 2016-07-19
4
58 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
  • 2
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 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 48

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now