|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is This a Correct Method ?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 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 trigger. I have elaborated a bit on this topic in http://www.karaszi.com/SQLServer/info_datetime.asp. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "jsfromynr" <jatinder.si***@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: Show quote > 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 trigger. 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.si***@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 > > Jatinder,
If I understand you correctly, you are saying that a trigger has the possible advantage of changing the datetime value that the user entered so that it always has 00:00:00 as the time portion. Where a check constraint will produce an error. Yes, that is a correct observation. You can't say that one approach is always 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 without the user or client application programmer knowing you have changed it. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "jsfromynr" <jatinder.si***@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: >> 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 trigger. 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.si***@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 >> > > 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 desired value. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "jsfromynr" <jatinder.si***@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 > |
|||||||||||||||||||||||
Other interesting topics