Solved

CONVERT DATETIME TO DATE

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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 …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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