CODEWITHSUNDEEP
phpsql-server
Ross
Ross

Reputation: 61

Removing time from a datetime field in a select query

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.

Upvotes: 0

Views: 650

Answers (3)

noman tufail
noman tufail

Reputation: 340

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.

Upvotes: 3

Accountant م
Accountant م

Reputation: 7523

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

Upvotes: 0

MJH
MJH

Reputation: 1750

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

Upvotes: 0

Related Questions