Use pubs
go
-- For SQL70
create table Invoice
(Inv_Dt datetime default (current_timestamp),
Inv_Dt_Date AS (CAST(CONVERT(varchar, Inv_Dt, 112) AS datetime)),
Inv_Dt_Time_With_MilliSeconds AS (CAST(CONVERT(varchar, Inv_Dt, 114) AS datetime)),
Inv_Dt_Time_Without_MilliSeconds AS (CAST(CONVERT(varchar, Inv_Dt, 108) AS datetime)),
Inv_Dt_Time_Hours_Minutes AS (CAST(SUBSTRING(CONVERT(varchar, Inv_Dt, 108), 1, 5) AS datetime)))
go
insert Invoice default values
select * from Invoice
-- For SQL42/60/65
create view vwInvoice AS
select Inv_Dt,
(CONVERT(datetime, CONVERT(varchar, Inv_Dt, 112))) AS Inv_Dt_Date,
(CONVERT(datetime, CONVERT(varchar, Inv_Dt, 114))) AS Inv_Dt_Time_With_MilliSeconds,
(CONVERT(datetime, CONVERT(varchar, Inv_Dt, 108))) AS Inv_Dt_Time_Without_MilliSeconds,
(CONVERT(datetime, SUBSTRING(CONVERT(varchar, Inv_Dt, 108), 1, 5))) AS Inv_Dt_Time_Hours_Minutes
from Invoice
go
select * from vwInvoice
go
drop view vwInvoice
drop table Invoice
go

This page was last updated on May 01, 2006 04:28 PM.