Many a times I have come across code snippets that either format a timestamp value for displaying to the user or compare with another timestamp value. Often its much easier to let the database do the same. However, since the database needs to use functions on the columns to do these conversions, indexes on these columns go unused thereby affecting performance.

To display the current time in a user-readable format, say Aug 24 2008 04:25 PM, the SimpleDateFormat class in Java can be used thus :


Timestamp currentTime = new Timestamp(System.currentTimeMillis());
SimpleDateFormat sdf = new SimpleDateFormat("MMM dd yyyy hh:mm a");
String currentTimeFormatted = sdf.format(currentTime);
System.out.println(currentTimeFormatted);


On the other hand, this can be easily achieved in a SQL query (MS SQL) :

SELECT CONVERT(CHAR, GETDATE()) AS 'FormattedDate';

The next one is another useful query (again, MS SQL) that I came across recently (for which I am yet to figure out an equivalent Java code !!) – this truncates the time part of the timestamp and makes it 00:00:00.000 in order to represent the midnight of that particular day.

SELECT CAST( FLOOR( CAST(GETDATE() AS FLOAT ) ) AS DATETIME) AS 'DateTimeAtMidnight';