Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update datetime field??

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 like

update emp set convert(varchar(50),date_t,121) = '2006-03-31 19:56:36.933'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'convert'.


please help me out

T.I.A

Shashank

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 08:58:09
That should be

update emp set date_t = '2006-03-31 19:56:36.933'

Why did you use convert function?
What is the datatype of date_t?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-06 : 09:40:31
hi....

My date_t column datatype is datetime....

T.I.A

Shashank
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 09:43:39
Then the above update worked?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 table

T.I.A

Shashank
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 09:48:20
Did you try the query I specified?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 also

T.I.A

Shashank
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 09:58:14
I dont think you can use 6 digit milliseconds. You can use maximum of 3 digits only
Read this
http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=15574

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 specified

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

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?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 follows
print convert(datetime,convert(varchar(23), '2006-03-31 19:56:36.933456'))
(U have to put it for update as necessary)

Srinika
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 10:35:26
To sort the duplicate issue make use of identity column

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-07 : 00:58:56
Doesnt your table have any other unique keys? Post table structure

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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:29PM


and if i fired like

select max(date_t) from emp

then what it select???and wheather its selection is logically correct

i am getting answer is Apr 7 2006 6:29PM
which is logicaaly wrong :( :(

pls help me out with solution

T.I.A

Go to Top of Page

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?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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:29PM


and if i fired like

select max(date_t) from emp

then what it select???and wheather its selection is logically correct

i am getting answer is Apr 7 2006 6:29PM
which is logicaaly wrong :( :(

pls help me out with solution

T.I.A





6:29PM is later than 8:34 AM ... you know that, right?
Go to Top of Page

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-07 : 10:08:05
hi..

even i checked for this
insert 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:29PM



i am getting answer is Apr 7 2006 6:29PM
which is logicaaly wrong :( :(


T.I.A
Go to Top of Page
    Next Page

- Advertisement -