Microsoft SQL Server 2008 - Dates

Question

I have a few questions regarding dates in SQL Server.

How to separate the value datetime"2011-08-10 14: 56: 17.267" in the date and time stamp in two separate columns. For instance. Date "2011-08-10" and timestamp "14:56:17"
I want to remove the timestamp from datetimein "2011-08-10" and still be able to order data by date (therefore it does not convert to varchar). It is also possible to change the date value as "August 10, 2011" and can still sort (not in alphabetical order, but in real time).

Thanks, HL

+5

Hl8 Aug 10 '11 at 5:02

2

Andriy M · Answer 1 · 2011-08-10T05:27:23+0000

:

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

, - , . , SELECT CONVERT . :

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

Alex_L · Answer 2 · 2011-08-10T05:07:48+0000

-- simple conversion example:
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) -- for date
SELECT CONVERT(VARCHAR(10), GETDATE(), 8)  -- for time