Microsoft SQL Server 2008 - Dates

Question

I have a couple of questions in regards to dates in SQL Server.

1. How do I separate a datetime value "2011-08-10 14:56:17.267" into date and timestamp in two separate columns. Eg. Date "2011-08-10" and timestamp "14:56:17"

2. I want remove the timestamp from datetime value into "2011-08-10" and still be able to order the data by date (therefore not converted to varchar). Also is there away to change the date value as '10 Aug 2011' and still can sort (not alphabetically but in real date order).

Thank you, HL

Answer 1

For the first one:

UPDATE atable
SET
  DateColumn = CAST(DateTimeColumn AS date),
  TimeColumn = CAST(DateTimeColumn AS time)

As for the second one, date display format is something that is unrelated to the date value. You can order the result set by your date column, but in the SELECT clause you can use CONVERT to display the date in the desired format. For example:

SELECT
  CONVERT(varchar, DateColumn, 106) AS Date,
  …
FROM atable
ORDER BY DateColumn