Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

SQL Getting date part of a DateTime field error

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
Murray Brown
Asked:
Murray Brown
2 Solutions
 
Robert SchuttSoftware EngineerCommented:
you can use CAST:
CAST([Date] as DATE) as TheDate

Open in new window

0
 
PortletPaulCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now