Splitting a date into 2 columns (date + time) in SQL

Question

I am trying to convert a Date key to my table, which is numeric in a date key key. My current request:

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 break it into two columns: Date: 2013-06-22

Time (Remove Microseconds): 13:36:44

Can someone modify my existing query to display the required output? That would be greatly appreciated. Please note: I am using SQL Server Management Studio 2008.

Answer 1

You can learn 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 them around your original SQL yields, 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 might want to move some of this into a view, just to reduce complexity.

Answer 2

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

Answer 3

You can use the STUFF function :

 DECLARE @MyTable TABLE([Date] VARCHAR(20)); INSERT @MyTable ([Date]) VALUES ('20130622133644403'); SELECT y.*, CONVERT(DATE,y.Date_AsDateTime) AS OnlyDate, CONVERT(TIME(0),y.Date_AsDateTime) AS OnlyTime FROM( SELECT x.[Date] AS Date_AsVarChar, CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(x.[Date],9,0,' '),12,0,':'),15,0,':'),18,0,'.')) AS Date_AsDateTime FROM @MyTable x ) y; 

Results:

 Date_AsVarChar Date_AsDateTime OnlyDate OnlyTime -------------------- ----------------------- ---------- -------- 20130622133644403 2013-06-22 13:36:44.403 2013-06-22 13:36:44 

Decision:

 SELECT CONVERT(DATE,y.Date_AsDateTime) AS OnlyDate, CONVERT(TIME(0),y.Date_AsDateTime) AS OnlyTime FROM( SELECT [Object], MsgId, Parms, CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(x.[Date],9,0,' '),12,0,':'),15,0,':'),18,0,'.')) AS Date_AsDateTime FROM JnlDataSection ) A WHERE MsgID = '325' -- SUBSTRING(Parms,1,6) = 'V40449' is not SARG-able AND Parms LIKE 'V40449%' ORDER BY DATE DESC; 

Answer 4

Use the following script to get the date, time, day, month, year, hours, minutes, seconds, AM / PM:

 select UpdatedOn , CONVERT(varchar,UpdatedOn,100) DateTime, CONVERT(varchar,UpdatedOn,10) Date , CONVERT(varchar,UpdatedOn,108) Time , substring(CONVERT(varchar,UpdatedOn,106),1,2) Day, substring(CONVERT(varchar,UpdatedOn,106),4,3) CMonth, substring(CONVERT(varchar,UpdatedOn,105),4,2) NMonth, substring(CONVERT(varchar,UpdatedOn,106),8,4) Year, left(right(CONVERT(varchar,UpdatedOn,100),7),2) Hours_12, substring(CONVERT(varchar,UpdatedOn,108),1,2) Hours_24, substring(CONVERT(varchar,UpdatedOn,108),4,2) Minutes, substring(CONVERT(varchar,UpdatedOn,108),7,2) Second, right(CONVERT(varchar,UpdatedOn,100),2) AM_PM from dbo.DeviceAssignSim where AssignSimId=55;