Solved

SQL Getting date part of a DateTime field error

Posted on 2014-10-17
3
194 Views
Last Modified: 2014-10-18
Hi
I am getting the following error in trying to run the SQL statement below. The column in a client's database
is a DateTime field
'DATE' is not a recognized built-in function name.

Select Phone, [First Name], [Machine operator],[Drilled To]-[Drilled From] As [Drilled m], DATE([Date]) as TheDate From Performance Inner Join People On [Machine operator] = [Operator COY] Where [Drilled To]-[Drilled From] > 10
0
Comment
Question by:murbro
[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
3 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 250 total points
ID: 40387993
you can use CAST:
CAST([Date] as DATE) as TheDate

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40388065
Depending on your SQL Server version you can output just the date without the time portion using CONVERT() or FORMAT()

SQL Server 2012 on:

FORMAT([date],'yyyy-mm-dd')

with the format() function you can alter the output to 'mm-dd-yyyy' or whatever you prefer


Without the format() function use CONVERT(varchar(length),[Date_field],style_number)

e.g.
CONVERT(varchar(10),[Date_field],120)

Here you need to know what the style numbers refer to, refer to a list of those is here. Note that in the above example the output is truncated at 10 characters hence you will not see any time portion.

If you use CAST([date_field] as date) then the default display format is applied, which might be, or might not be, the desired outcome
0
 

Author Closing Comment

by:murbro
ID: 40388648
Thanks
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

734 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