Author |
Topic |
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 08:48:04
|
hello friend !!i want to update date field but i am getting error likeupdate emp set convert(varchar(50),date_t,121) = '2006-03-31 19:56:36.933'Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'convert'.please help me outT.I.AShashank |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-06 : 08:58:09
|
That should beupdate emp set date_t = '2006-03-31 19:56:36.933'Why did you use convert function?What is the datatype of date_t?MadhivananFailing to plan is Planning to fail |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 09:40:31
|
hi....My date_t column datatype is datetime....T.I.AShashank |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-06 : 09:43:39
|
Then the above update worked?MadhivananFailing to plan is Planning to fail |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 09:46:05
|
hi..for that i need your help..how i update my emp tableT.I.AShashank |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-06 : 09:48:20
|
Did you try the query I specified?MadhivananFailing to plan is Planning to fail |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 09:53:21
|
thanks!!!that worked!!! is there any kind of way that update my table (in sql server 2005)update emp set date_t = '2006-03-31 19:56:36.933456'i m taking 6 digit milliseconds alsoT.I.AShashank |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-04-06 : 09:56:50
|
try update emp set date_t = convert(datetime,'2006-03-31 19:56:36.933456')Srinika |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-06 : 09:59:32
|
quote: Originally posted by Srinika try update emp set date_t = convert(datetime,'2006-03-31 19:56:36.933456')Srinika
No that wont work Read the article I specifiedMadhivananFailing to plan is Planning to fail |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 10:06:05
|
hi..thanks for ur guidance...I agree that we cant use 6 digit milliseconds but if i change my data type from datetime to varchar(45) then is this feasible means changing datatype from datetime to varchar(45) is benefit as far as my 6 digit issue consideration???T.I.A |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-06 : 10:11:02
|
No. Varchar datatype should not be used to store dates just because it stores what you want Why do you want to store 6 digits miliiseconds?MadhivananFailing to plan is Planning to fail |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-04-06 : 10:12:23
|
Yes Madhi,You are correct.under2811, Chnging of datatype is not recommended.If u r not worried much about the precision, u may use it as followsprint convert(datetime,convert(varchar(23), '2006-03-31 19:56:36.933456'))(U have to put it for update as necessary)Srinika |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 10:30:40
|
hi..we are using this field to sort the duplicate issue....if this field having same values then we mark it as duplicate so for accuracy purpose we need 6 digit milliseconds and we need to implement it as early as possible so pls help me out if u have way!!!T.I.A |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-06 : 10:35:26
|
To sort the duplicate issue make use of identity columnMadhivananFailing to plan is Planning to fail |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-06 : 10:38:16
|
hi..our client dont wanna to add new column here so we have think on this issue only....is there any hopes????T.I.A |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-07 : 00:58:56
|
Doesnt your table have any other unique keys? Post table structureMadhivananFailing to plan is Planning to fail |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-07 : 09:00:03
|
hi...Actually we have two PKs and this DATE (default getdate()) field along with other fields.ok what we doing, we are inserting the records into our master table from this table.and only unique records we are inserting.suppose sometime it may have situation like same PK but we have different date in that case we are inserting records having MAX(DATE_T) but last few months we are facing problem that same PKs and this DATE field also same.so for accuracy purpose we need to take it 6 digit.Please guide me if i m in wrong way say if i change its data type from DATETIME to varchar(45) then in that if i want to compare this DATE field by MAX(DATE_T) then how varchar behave for example i have records for this field are....insert into emp(id,date_t) values(72,'04/07/2006 8:34:34.123456')insert into emp(id,date_t) values(52,'04/07/2006 8:34:34.123456')insert into emp(id) values(22) ---default is getdate()-- value Apr 7 2006 6:29PMand if i fired like select max(date_t) from empthen what it select???and wheather its selection is logically correcti am getting answer is Apr 7 2006 6:29PMwhich is logicaaly wrong :( :(pls help me out with solutionT.I.A |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-07 : 09:03:14
|
Thats why you need to use proper datetime datatype. Do you think you get conflict with composite PKs when you use only three digits in milliseconds?MadhivananFailing to plan is Planning to fail |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-04-07 : 09:10:24
|
quote: Originally posted by under2811 hi...Actually we have two PKs and this DATE (default getdate()) field along with other fields.ok what we doing, we are inserting the records into our master table from this table.and only unique records we are inserting.suppose sometime it may have situation like same PK but we have different date in that case we are inserting records having MAX(DATE_T) but last few months we are facing problem that same PKs and this DATE field also same.so for accuracy purpose we need to take it 6 digit.Please guide me if i m in wrong way say if i change its data type from DATETIME to varchar(45) then in that if i want to compare this DATE field by MAX(DATE_T) then how varchar behave for example i have records for this field are....insert into emp(id,date_t) values(72,'04/07/2006 8:34:34.123456')insert into emp(id,date_t) values(52,'04/07/2006 8:34:34.123456')insert into emp(id) values(22) ---default is getdate()-- value Apr 7 2006 6:29PMand if i fired like select max(date_t) from empthen what it select???and wheather its selection is logically correcti am getting answer is Apr 7 2006 6:29PMwhich is logicaaly wrong :( :(pls help me out with solutionT.I.A
6:29PM is later than 8:34 AM ... you know that, right? |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-04-07 : 10:08:05
|
hi..even i checked for thisinsert into emp(id,date_t) values(72,'04/07/2006 20:34:34.123456')insert into emp(id,date_t) values(52,'04/07/2006 20:34:34.123456')insert into emp(id) values(22) ---default is getdate()-- value Apr 7 2006 6:29PMi am getting answer is Apr 7 2006 6:29PMwhich is logicaaly wrong :( :(T.I.A |
|
|
Next Page
|