how to remove time from datetime

Question

The field DATE in the database has the following format   2012-11-12 00 00 00   I would like to remove the time from the date and return the date like this   11 12 2012

User · Answer

You may try the following   SELECT CONVERT VARCHAR 10  yourdate 101     or this   select cast floor cast urdate as float   as datetime

User · Answer

just use   in TSQL   SELECT convert varchar  columnName  101    in MySQL  SELECT DATE FORMAT columnName    m  d  Y

User · Answer

TSQL  SELECT CONVERT DATE  GETDATE               2019-09-19 SELECT CAST GETDATE   AS DATE              2019-09-19 SELECT CONVERT VARCHAR  GETDATE    23      2019-09-19

User · Answer

Personally  I d return the full  native datetime value and format this in the client code   That way  you can use the user s locale setting to give the correct meaning to that user    11 12  is ambiguous  Is it    12th November 11th December

User · Answer

Use this SQL   SELECT DATE FORMAT date column here   d  m  Y   FROM table name

User · Answer

SELECT DATE  2012-11-12 00 00 00      returns  2012-11-12

User · Answer

In mysql at least  you can use DATE theDate

User · Answer

For more info refer this  SQL Server Date Formats   MM DD YYYY   SELECT CONVERT VARCHAR 10   cast dt col as date   101  from tbl    DD MM YYYY   SELECT CONVERT VARCHAR 10   cast dt col as date   103  from tbl   Live Demo

User · Answer

First thing s first  if your dates are in varchar format change that  store dates as dates it will save you a lot of headaches and it is something that is best done sooner rather than later  The problem will only get worse   Secondly  once you have a date DO NOT convert the date to a varchar  Keep it in date format and use formatting on the application side to get the required date format    There are various methods to do this depending on your DBMS     SQL-Server 2008 and later   SELECT  CAST CURRENT TIMESTAMP AS DATE      SQL-Server 2005 and Earlier  SELECT  DATEADD DAY  DATEDIFF DAY  0  CURRENT TIMESTAMP   0      SQLite  SELECT  DATE NOW        Oracle  SELECT  TRUNC CURRENT TIMESTAMP      Postgresql  SELECT  CURRENT TIMESTAMP  DATE     If you need to use culture specific formatting in your report you can either explicitly state the format of the receiving text box  e g  dd MM yyyy   or you can set the language so that it shows the relevant date format for that language   Either way this is much better handled outside of SQL as converting to varchar within SQL will impact any sorting you may do in your report   If you cannot will not change the datatype to DATETIME  then still convert it to a date within SQL  e g  CONVERT DATETIME  yourField   before sending to report services and handle it as described above

User · Answer

I found this method to be quite useful  However it will convert your date time format to just date but never the less it does the job for what I need it for   I just needed to display the date on a report  the time was irrelevant    CAST start date AS DATE    UPDATE   Bear in mind I m a trainee      I figured an easier way to do this IF YOU RE USING SSRS   It s easier to actually change the textbox properties where the field is located in the report  Right click field Number Date and select the appropriate format

[sql] how to remove time from datetime

Examples related to sql

Examples related to date

Examples related to reporting-services