SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update datetime field??
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

under2811
Constraint Violating Yak Guru

India
348 Posts

Posted - 04/06/2006 :  08:48:04  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/06/2006 :  08:58:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
348 Posts

Posted - 04/06/2006 :  09:40:31  Show Profile  Reply with Quote
hi....

My date_t column datatype is datetime....

T.I.A

Shashank
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 04/06/2006 :  09:43:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Then the above update worked?

Madhivanan

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

under2811
Constraint Violating Yak Guru

India
348 Posts

Posted - 04/06/2006 :  09:46:05  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/06/2006 :  09:48:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
348 Posts

Posted - 04/06/2006 :  09:53:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 04/06/2006 :  09:56:50  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/06/2006 :  09:58:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
22713 Posts

Posted - 04/06/2006 :  09:59:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
348 Posts

Posted - 04/06/2006 :  10:06:05  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/06/2006 :  10:11:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 04/06/2006 :  10:12:23  Show Profile  Reply with Quote
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

India
348 Posts

Posted - 04/06/2006 :  10:30:40  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/06/2006 :  10:35:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
348 Posts

Posted - 04/06/2006 :  10:38:16  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/07/2006 :  00:58:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

India
348 Posts

Posted - 04/07/2006 :  09:00:03  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/07/2006 :  09:03:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
7423 Posts

Posted - 04/07/2006 :  09:10:24  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

India
348 Posts

Posted - 04/07/2006 :  10:08:05  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000