how to seperate date and time in mssql table in to two columns

Question

i have view having a date column that includes both date and time ,i want seperate that columns in to date and time columns

I tried the getdate function its not working , here is my example sql table data

title  description       date
sample  this is sample   2018-11-08 23:59:59.000

i want to have

date           time 
2018-11-08    23:59

Yogesh Sharma · Answer 1 · 2019-02-08T19:13:03.723

1

You can do :

select cast(date as date), left(cast(date as time), 5)

You can easily do conversation with simple SELECT statement.

If you want separate date & time, then use ALTER statement to modify :

alter table t
     add [date] as CAST( [date] as date),
     add [time] as CAST( [date] as time(0))

edited Feb 08 '19 at 19:13

answered Feb 08 '19 at 19:06

Yogesh Sharma

49,870
5
26
52

i am getting error type date is not define system type and type time is not defined system type – vyshnavi Feb 08 '19 at 19:16
@vyshnavi. . So, you are using lower version of `SQL Server` ?? – Yogesh Sharma Feb 08 '19 at 19:18
mine is mssql 9.0.5057 version – vyshnavi Feb 08 '19 at 19:23
1

@vyshnavi that version doesn't support `date` it's introduced in SQL Server 2008 (version 10), also the database itself is not supported anymore. You should upgrade. – Mark Baijens Feb 08 '19 at 19:32

score 0 · Answer 2 · answered Feb 08 '19 at 19:08

Since you probably might need to use the date and time together, I'd suggest to create 2 computed columns. e.g.

CREATE TABLE SampleData(
    title       varchar(15),
    description varchar(100),
    [date]      datetime
)
INSERT INTO SampleData
VALUES( 'sample', 'this is sample', '2018-11-08 23:59:59.000')

ALTER TABLE SampleData
    ADD dateonly AS CAST( [date] as date),
        timeonly AS CAST( [date] as time)

SELECT * FROM SampleData;

how to seperate date and time in mssql table in to two columns

2 Answers2