how to remove time from datetime

by

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.

More Related Contents:

  1. Current date in Oracle Db
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Best approach to remove time part of datetime in SQL Server
  4. Count work days between two dates
  5. Left Outer Join Not Working?
  6. Oracle’s default DATE format
  7. How to determine the number of days in a month in SQL Server?
  8. When to use VARCHAR and DATE/DATETIME
  9. How to convert an integer (time) to HH:MM:SS::00 in SQL Server 2008?
  10. How do you do date math that ignores the year?
  11. Select data from date range between two dates
  12. Find closest date in SQL Server
  13. Query comparing dates in SQL
  14. Getting date list in a range in PostgreSQL
  15. Sql PIVOT and string concatenation aggregate
  16. How to compare two dates to find time difference in SQL Server 2005, date manipulation
  17. How to calculate the number of “Tuesdays” between two dates in TSQL?
  18. SQL SERVER: Get total days between two dates
  19. Get month name from date in Oracle
  20. Add business days to date in SQL without loops
  21. SQL ORDER BY date problem
  22. divide by zero/null workaround in SSRS 2008 report
  23. Comparing with date in Oracle sql
  24. Check whether string is a date Postgresql
  25. How do I format date and time on ssrs report?
  26. Return number from Oracle Select statement after parsing date
  27. Postgresql query between date ranges
  28. Select query with date condition
  29. Oracle Convert TIMESTAMP with Timezone to DATE
  30. Date serial in SQL?

Leave a Comment