Removing time from a datetime field in a select query

Question

I have the following SQL query to use on my website and I want to remove the time from the datetime column 'Date_Required' when the resulting table is displayed:

$query = "SELECT Job_No, Sub_No, Visit_Status, Engineer, CAST(Date_Required AS DATE) FROM dbo.VSMF_SERVICE_VISITS WHERE Visit_Status = 'O' and Engineer='*AY' and Date_Required >= '2018-01-01 00:00:00.000' ORDER BY Date_Required DESC";

So it's displayed as "Jan 01 2018" instead of "Jan 01 2018 12:00:00:000PM"

The query is in a PHP file.

do this in the presentation layer, not in the database layer. — Zohar Peled, Mar 05 '18 at 08:23
@ZoharPeled - I'm doing this in a PHP file so I don't think that would work? — Ross, Mar 05 '18 at 08:27
[https://stackoverflow.com/a/267732/5407848](https://stackoverflow.com/a/267732/5407848) — Accountant م, Mar 05 '18 at 08:34
@Accountantم - They just show selecting one column not multiple columns like mine. — Ross, Mar 05 '18 at 08:36
You can specify the fields to be selected one by one and use `CAST(myDatetimeCol AS Date)` to convert the datetime field into date. It's generally a bad practice anyway using `*` in `SELECT` clause. — Giorgos Betsos, Mar 05 '18 at 08:40
sorry I didn't notice sql-server. check [this](https://stackoverflow.com/questions/5222075/sql-datetime-format-to-date-only) — Accountant م, Mar 05 '18 at 08:40
I've edited my query and no results are displayed for the date column now. — Ross, Mar 05 '18 at 08:47

noman tufail · Accepted Answer · 2018-03-05T09:31:58.450

3

Try this one.

$query = "SELECT CAST(Date_Required AS DATE) as Date_Required FROM dbo.VSMF_SERVICE_VISITS WHERE Visit_Status = 'O' and Engineer='*AY' and Date_Required >= '2018-01-01 00:00:00.000' ORDER BY Date_Required DESC";

I hope this will work for you. If you want to select all the columns in the table then mention them in the select statement one by one like this

SELECT id, name, CAST(Date_Required AS DATE) as Date_Required from ...

using * will be more tricky.

edited Mar 05 '18 at 09:31

answered Mar 05 '18 at 08:46

noman tufail

341
2
8

Thank you for the answer, but I get no results when I run that query. – Ross Mar 05 '18 at 08:51
was there any exception ? – noman tufail Mar 05 '18 at 08:59
okay try this one. $query = "SELECT DATE(\`Date_Required\`) as Date_required FROM dbo.VSMF_SERVICE_VISITS WHERE Visit_Status = 'O' and Engineer='*AY' and Date_Required >= '2018-01-01 00:00:00.000' ORDER BY Date_Required DESC"; – noman tufail Mar 05 '18 at 09:02
I think problem is in some single quote. Otherwise this query is working fine in phpmyadmin sql editor. – noman tufail Mar 05 '18 at 09:03
No that doesn't work either, blank again with no exception. – Ross Mar 05 '18 at 09:04
You can try this `DATE(\`Date_Required\`)` technique on a simple table in your sql editor and than i think you can better understand how to make it work in your current problem. – noman tufail Mar 05 '18 at 09:07
okay once more try removing backticks like this. `$query = "SELECT DATE(Date_Required) as Date_required FROM dbo.VSMF_SERVICE_VISITS WHERE Visit_Status = 'O' and Engineer='*AY' and Date_Required >= '2018-01-01 00:00:00.000' ORDER BY Date_Required DESC";` – noman tufail Mar 05 '18 at 09:09
I found the error, it says "DATE is not a recognised built in function name." – Ross Mar 05 '18 at 09:12
Okay try this one. `CAST(Date_Required AS DATE)` instead of `DATE(Date_Required) as Date_required`. I never worked on sql server but you can try this. – noman tufail Mar 05 '18 at 09:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166224/discussion-between-ross-h-and-noman-tufail). – Ross Mar 05 '18 at 09:19

score 0 · Answer 2 · answered Mar 05 '18 at 08:44

0

If you are determined to do this in SQL, then have a read of this post (which it took me a couple of seconds to find):

Best approach to remove time part of datetime in SQL Server

answered Mar 05 '18 at 08:44

MJH

1,710
1
9
19

I'm struggling with selecting multiple columns and having multiple where conditions. The link you gave only shows one select and no where conditions. – Ross Mar 05 '18 at 08:48

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

If you want to do this with PHP you have several options, cutting the first 10 chars of the string by substr or using the powerful DateTime class

<?php
$dateTime = "2018-03-05 01:30:00";

echo substr($dateTime, 0 , 10);// option 1 
echo "\n";
$dateTimeObj = new DateTime($dateTime);// option 2 
echo $dateTimeObj->format("Y-m-d");

this outputs

2018-03-05

2018-03-05

live demo

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 05 '18 at 08:51

Accountant م

6,975
3
41
61

Thank you, but the date is a variable from an SQL Server database not a hardcoded date. – Ross Mar 05 '18 at 08:57
the `$dateTime` is for demonstration. Replace it with your actual value at the presentation layer (right before you put it in the HTML) – Accountant م Mar 05 '18 at 09:02

Removing time from a datetime field in a select query

3 Answers3