how to remove time from datetime
Solution 1
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.
Solution 2
just use, (in TSQL)
SELECT convert(varchar, columnName, 101)
in MySQL
SELECT DATE_FORMAT(columnName, '%m/%d/%Y')
Solution 3
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!
Solution 4
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
Solution 5
SELECT DATE('2012-11-12 00:00:00');
returns
2012-11-12
Related videos on Youtube
user1860212
Updated on July 09, 2022Comments
-
user1860212 almost 2 years
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
-
Kevin Brydon over 11 yearsWhat database are you using? MySQL? SQL Server? Access? Oracle?
-
Gordon Linoff over 11 yearsThink about keeping the date as 2012-11-12. This is the ISO standard format and is more useful than other formats -- even for end users -- because you sort by the string and have the dates in the right order.
-
Mark Ransom over 11 years@GordonLinoff, my favorite attribute of the ISO format is that there's never any ambiguity about whether the month or day comes first. That's extremely important if you have users in different countries.
-
Gordon Linoff over 11 years@MarkRansom . . . Unfortunately, in SQL Server, that is only true when you leave out the dashes. So, '20121112' is always "12 Nov 2012", regardless of date format settings on the server. It is possible to have '2012-11-12' mean either 11 Dec 2012 or 12 Nov 2012, but I still prefer the dashes. Here is a good blog on ths subject by Aaron Bertrand . . . sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/….
-
Mark Ransom over 11 years@GordonLinoff, are there really locales in the world where YYYY-DD-MM (versus YYYY/DD/MM) is considered proper? If not then that would be a bug in SQL Server.
-
Gordon Linoff over 11 years@MarkRansom . . . Read what Aaron has to say. He is an expert on internationalization and date formats. But, as I said, I am human so I put in the dashes.
-
Mark Ransom over 11 years@GordonLinoff, I took his comments to mean "this is how this format is interpreted by SQL Server in a French locale", not "this is how the French commonly specify dates". No doubt he could render an opinion on whether it should be considered a bug or not, but that wasn't the point of his post.
-
-
Martijn Pieters over 11 years@madth3: Actually, it does provide an answer, just an incorrect one.
-
user1860212 over 11 yearsThanks, it works for me with a date field, but not with Char type field. any idea what I need to add to the command ? thanks
-
user1860212 over 11 yearsThanks, it works for me with a date field, but not with Char type field. any idea what I need to add to the command in TSQL ? thanks
-
user1860212 over 11 yearsThanks, the first command works for me with a date field, but not with Char type field. any idea what I need to add to the command ? thanks
-
bonCodigo over 11 years@user1860212 Then you gotta convert the the char type to date and then remove it... Are you asking in TSQL? Try this please: (PS: I have a very slow connection so please bear with it)
cast(convert(char(8),urdate,112) as datetime)
-
Mark Ransom over 11 years@user1860212, you need to do a double conversion - char to datetime, then datetime to char.