Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to split the date time field into two? Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2014 2:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:55 PM
Points: 30, Visits: 98
Hi team,

I have a date time column and it show as follows " 2014-03-26 10:55". I want to write an expression to split this into columns, so that the date is separate from time.

i/p is Datetime column - "2014-03-26 10:55"

o/p is date column - 2014-03-26 and time column- 10:55 and it should be in 24 hr format.

could some one help me with the logic.

Thanks.
Post #1554829
Posted Wednesday, March 26, 2014 3:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
You can use CONVERT for that.

DECLARE @TestDatetime DATETIME = GETDATE();
DECLARE @TestDate DATE;
DECLARE @TestTime TIME;

SELECT @TestDate = CONVERT(DATE,@TestDatetime);
SELECT @TestTime = CONVERT(TIME,@TestDatetime);

PRINT @TestDate;
PRINT @TestTime;

If you want the values inserted in seperate columns, you can let SQL Server do the heavy lifting:

CREATE TABLE #Test(TestDate DATE, TestTime TIME);
INSERT INTO #Test(TestDate,TestTime) VALUES (GETDATE(),GETDATE());

SELECT * FROM #Test;

edit: I noticed after posting this that your question was in a Reporting Services forum. If you use SQL Server as a source, you can do this with the TSQL I posted. If not, you can use the FORMAT function in SSRS.
Here's a nice article explaining all about it:
Formatting Dates [SSRS]




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1554835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse