split a datetime into a date and a time in a view?

Hello,

I would like to store begin and end of something as datetime in a table.

Can I create a view that contains begin_date, begin_time, end_date and end_time, derived from the original table?

Thanks

Magnus

March 27th, 2015 8:33am

Yes it is possible.

create table mytable (begindate datetime,enddate datetime)
insert into mytable values(getdate(), getdate()+12)


create view myview
as
 select 
 convert(date, begindate) as begin_date, 
 convert(varchar(8), convert(time, begindate)) as begin_time,
  convert(date, enddate) as end_date, 
 convert(varchar(8), convert(time, enddate)) as end_time
 from mytable

 go


 drop view myview

Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 8:43am

Thanks!

I can do a SELECT the way you proposed, but when I try to make a view, I get:

"Cannot call methods on date"

What's wrong?

Magnus

March 27th, 2015 9:08am

Hello,

I would like to store begin and end of something as datetime in a table.

Can I create a view that contains begin_date, begin_time, end_date and end_time, derived from the original table?

Thanks

Magnus

For instance, you get the string below

select CONVERT(varchar,getdate(),113)
go
13 Aug 2009 14:35:05:347

using convert, then you can further use substring to get date and time separately, like

select substring(CONVERT(varchar,getdate(),113),1,12) [Date], substring(CONVERT(varchar,getdate(),113),13,12) [Time]
go
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 9:22am

It seems like a known issue. Please try as specify in the below link,

http://stackoverflow.com/questions/16180347/sql-server-cannot-call-methods-on-date

March 28th, 2015 8:06am

Always state what version you are using

create table #t (dt datetime)

insert into #t values ('20150329 12:25'),('20150329 15:40')


select cast(dt as date) st_dt, cast(dt as time) as sttime
from #t

Free Windows Admin Tool Kit Click here and download it now
March 29th, 2015 1:33am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics