Solved

CONVERT DATETIME TO DATE

Posted on 2016-07-19
4
70 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

778 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