Is This a Correct Method ?

Hi All,
While inserting date value I wish to take only date part So I tried
this
Create Table JTrial
(
XYZ int,
d datetime default convert(varchar,getdate(),112)
)
insert into JTrial(XYZ) values(1)
insert into JTrial(XYZ) values(2)
insert into JTrial(XYZ) values(3)
insert into JTrial(XYZ) values(4)
select * from JTrial
Is there any better alternative. Check constraint like this
check ( d = convert(varchar,d,112) )
Will not allow me to insert row
insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has
time part
Is writing A trigger better alternative ?
Please guide me on this ?
With warm regards
Jatinder SinghYes, that is the method that I prefer (although I always specify a length fo
r varchar, see your
convert function). I also like to have a check constraint instead of a trigg
er. I have elaborated a
bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124441020.097121.43310@.f14g2000cwb.googlegroups.com...
> Hi All,
> While inserting date value I wish to take only date part So I tried
> this
> Create Table JTrial
> (
> XYZ int,
> d datetime default convert(varchar,getdate(),112)
> )
> insert into JTrial(XYZ) values(1)
> insert into JTrial(XYZ) values(2)
> insert into JTrial(XYZ) values(3)
> insert into JTrial(XYZ) values(4)
> select * from JTrial
> Is there any better alternative. Check constraint like this
> check ( d = convert(varchar,d,112) )
> Will not allow me to insert row
> insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has
> time part
> Is writing A trigger better alternative ?
> Please guide me on this ?
> With warm regards
> Jatinder Singh
>|||Hi Tibor,
The default constraint work with a value (fix sort of/ not entered by
user) of date and Check constraint does not let it pass if it has any
time other than (00:00:00), Both in what your article suggest and what
I tried.
Thanks for giving valuable advice , I purposed Trigger because I
would storage there.
Create Table JTrial
(
XYZ int,
d datetime default convert(varchar,getdate(),112)
)
Go
Create trigger trg1 on JTrial for Insert
as
Begin
Update JTrial set d=convert(varchar,d,112)
-- I should have take a cross join with the Inserted table
End
Declare @.aDate datetime
select getdate()
set @.aDate = '2005-08-19 18:17:09.607'
insert into JTrial(XYZ,d) values(1,getdate())
insert into JTrial(XYZ,d) values(2,@.aDate) -- User entered value
insert into JTrial(XYZ) values(3) -- Default will be stored
insert into JTrial(XYZ) values(4)
select * from JTrial
Drop table JTrial
With warm regards
Jatinder Singh
Tibor Karaszi wrote:
> Yes, that is the method that I prefer (although I always specify a length
for varchar, see your
> convert function). I also like to have a check constraint instead of a tri
gger. I have elaborated a
> bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
> news:1124441020.097121.43310@.f14g2000cwb.googlegroups.com...|||Jatinder,
If I understand you correctly, you are saying that a trigger has the possibl
e advantage of changing
the datetime value that the user entered so that it always has 00:00:00 as t
he time portion. Where a
check constraint will produce an error.
Yes, that is a correct observation. You can't say that one approach is alway
s correct. I prefer the
check constraint, as you will catch where applications is sending an invalid
datetime value and fix
the application. IMO, that is a better approach to just changing the value w
ithout the user or
client application programmer knowing you have changed it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124455793.019933.100900@.g43g2000cwa.googlegroups.com...
> Hi Tibor,
> The default constraint work with a value (fix sort of/ not entered by
> user) of date and Check constraint does not let it pass if it has any
> time other than (00:00:00), Both in what your article suggest and what
> I tried.
> Thanks for giving valuable advice , I purposed Trigger because I
> would storage there.
> Create Table JTrial
> (
> XYZ int,
> d datetime default convert(varchar,getdate(),112)
> )
> Go
> Create trigger trg1 on JTrial for Insert
> as
> Begin
> Update JTrial set d=convert(varchar,d,112)
> -- I should have take a cross join with the Inserted table
> End
> Declare @.aDate datetime
> select getdate()
> set @.aDate = '2005-08-19 18:17:09.607'
> insert into JTrial(XYZ,d) values(1,getdate())
> insert into JTrial(XYZ,d) values(2,@.aDate) -- User entered value
> insert into JTrial(XYZ) values(3) -- Default will be stored
> insert into JTrial(XYZ) values(4)
> select * from JTrial
> Drop table JTrial
> With warm regards
> Jatinder Singh
> Tibor Karaszi wrote:
>|||Sorry Typo
inner join in trigger
With warm regards
Jatinder Singh|||Tibor,
Thanks for giving your time. Is speed /performance an issue
here means using trigger v/s Conversions at client side.
With warm regards
Jatinder Singh|||Yes, passing in the correct data to begin with will give better performance
compared to having a
trigger which goes back to the modified rows and alter the value to the desi
red value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1124457867.638474.309950@.g14g2000cwa.googlegroups.com...
> Tibor,
> Thanks for giving your time. Is speed /performance an issue
> here means using trigger v/s Conversions at client side.
> With warm regards
> Jatinder Singh
>