Splitting Date into 2 Columns (Date + Time) in SQL

Question

I'm trying to convert the Date key in my table which is numeric into date time key. My current query is:

  SELECT 
  DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
  SUBSTRING([Date],10,2)+':'+
  SUBSTRING([Date],12,2)+':'+
  SUBSTRING([Date],14,2)+'.'+
  SUBSTRING([Date],15,3))) [Date],
  [Object] AS [Dataset],
  SUBSTRING(Parms,1,6) AS [Media]
  FROM (Select CONVERT(VARCHAR(18),[Date]) [Date], 
  [Object],
  MsgId,
  Parms
  FROM JnlDataSection) A
  Where MsgID = '325' AND
  SUBSTRING(Parms,1,6) = 'V40449' 
  Order By Date DESC;

The Date Column shows this: 2013-06-22 13:36:44.403

I want to split this into two columns: Date: 2013-06-22

Time (Remove Microseconds): 13:36:44

Can anyone modify my existing query to display the required output? That would be greatly appreciated. Please Note: I'm using SQL Server Management Studio 2008.

Answer 1

SELECT CONVERT(DATE,[Date])
SELECT CONVERT(TIME(0),[Date])

Answer 2

You may want to investigate the convert() function:

  select convert(date, getdate()) as [Date], convert(varchar(8), convert(time, getdate())) as [Time]

gives

  Date       Time
  ---------- --------
  2013-07-16 15:05:43

Wrapping these around your original SQL gives the admittedly very ugly:

SELECT convert(date, 
      DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
        SUBSTRING([Date],10,2)+':'+
        SUBSTRING([Date],12,2)+':'+
        SUBSTRING([Date],14,2)+'.'+
        SUBSTRING([Date],15,3)))) [Date],
  convert(varchar(8), convert(time, 
      DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
        SUBSTRING([Date],10,2)+':'+
        SUBSTRING([Date],12,2)+':'+
        SUBSTRING([Date],14,2)+'.'+
        SUBSTRING([Date],15,3))))) [Time],

  [Object] AS [Dataset],
  SUBSTRING(Parms,1,6) AS [Media]
  FROM (Select CONVERT(VARCHAR(18),[Date]) [Date], 
  [Object],
  MsgId,
  Parms
  FROM JnlDataSection) A
  Where MsgID = '325' AND
  SUBSTRING(Parms,1,6) = 'V40449' 
  Order By Date DESC;

You may want to move part of this into a view, just to reduce complexity.