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

how to separate date and time in mysql
how to separate date and time from timestamp in sql
how to separate date and time in oracle
extract date from datetime sql
sql convert datetime to date
how to split date column in sql
access query split date and time
extract day from date in sql

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.

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.

Split Date and Time in sql query, How do I split a date into month and year in SQL? how to Split DateTime value in seprate date and time column – Learn more on the SQLServerCentral forums with split DateTime value in two separate column in Sql. the date and time in one

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

How to Extract a Year from a Date in SQL, be a character, binary, text, ntext, or image expression. I want to split a datetime column so that the year and the month both have their own column in a select statement output. I also want to have a column by week of the year, as opposed to specific date. Basically, I want separate year, month, and week columns to show up in my select statement output.

You could use 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

Solution:

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;

SQL Server SUBSTRING Function By Practical Examples, I have problem with split DateTime value in two separate column in Sql. Example​-- Suppose in DateTime field Name dtmDate it hold the value  489 2 2 gold badges 5 5 silver badges 11 11 bronze badges If you can alter the design at this point then I suggest that you do so. Save the names into two fields to begin with.

Use the following scenario for getting 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;

how to Split DateTime value in seprate date and time column , You can always split the information needed from a single column so adding two separate columns for a single point-in-time makes things  I have mentioned 3 different methods to separate date and time from datetime but if you have SQL Server 2008 and above, then I would recommend Method 2.The reason behind this recommendation is in Method 2 we just convert datetime into a date type and time type but in other methods we first convert datetime into date type and time type and then

[Solved] How to split date n time from datetime in SQL table using , How to separate date and time from DateTime ? I came across this question many times in multiple blogs. Given below are different methods to  The second use of date with input 1) does the conversion of the first 9 characters to a SAS date value and 2) stores the resulting number of days into a the previously created character variable. If you remove the first date= statement then you will have a numeric value from the data=input( Then add the statement Format date date9.;

SQL SERVER – Multiple ways to get Date and Time separately from , (If you have a separate column again for the offset, then I will give you a hug next SalesOrderHeader table, you'll see a datetime column called be a computed column joining the two (which you should do by adding the  SQL SERVER – How to split one column into multiple columns. August 22, 2015 by Muhammad Imran. Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery.

Dealing with date and time instead of datetime, 9/14/2005 2:38:23 AM I would like to split this into a DATE and a TIME column. I believe I should use an Update command and it has to do with  Split date and time with Text to Column In Excel, you also can use Text to Column to split the Date and Time column to columns. 1. Select the data range and click Data > Text to Columns, then in the popping dialog, check Delimited option.

Comments
  • how would I get it to display the required output from the question?
  • Replace getdate() with the [Date] field in your original SQL. I'll update the answer slightly to include your original question.
  • And where would I add that in my query? is it possible for you to add that in my existing query and then post it as an answer so I'm able to test it right away.
  • Can you illustrate what the [Date] column is in your source data, so I have a value to test against? Ta.
  • Initially it was 20130622.133644403. But so far I've converted it to 2013-06-22 13:36:44.403
  • Is it possible for you to add that logic to my existing query? To only display OnlyDate and OnlyTime?