convert datetime (hh:mm)

hi,
i have date format in mm/dd/yy hh:mm:ss in my database.
for example a date field is presently looks like 01/04/99 12:00:00. but i want to convert the same datefield to 01/04/99 11:59:00. could anybody help in writing query to update the that particular date field to my required format. i am using
sybase 10.2....
01/04/99 12:00:00 To 01/04/99 11:59:00
thanX
hmk
hmkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajith_29Commented:
lets name the field to upadate has time_upadate
and the table name is upadate_table
>update update_table
>set time_update = '01/04/1999 11:59:00'
>where time_update = '01/04/199 12:00:00'
>go


This is solve u problem
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kavitagCommented:
update table

set date_field = dateadd(mi,-1,dateadd(dd,1,datefield))
where date_field = '1/4/99'


when hh and mi are not specified it assumes (12:00 AM)midnight time.
0
kavitagCommented:
Ignore the previous comment. That is good if the field is datetime format.

is it char or datetime format?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

dk99Commented:
If the field data type is datetime,you can use the following query too :

update table_name
set
date_field = dateadd(mi,-1,date_field)
where  
convert(char(8),date_field,1)="01/04/99"
and datepart(mi,date_field)=0
0
TahirKhalilCommented:
Yes !  You can use

Update Table_Name
Set  Date_Field = DateAdd(Mi, -1, Date_Field)
Where Date_Field = 'Jan 4 99' /* If you wanna change only this date... Otherwise there is no need to check date */


0
ahoorCommented:
Of course the dateadd(mi,-1) does not work, since the
date must stay the same...

what you can do is (considering the jan 4 is an example)

update table_name
set       date_field =     convert(datetime,convert(char(10),date_filed,112) + '11:59:00')
go

If you really only need to update the jan 4th you can also use
the answer of ajith29. (Or add a where clause to my update statement saying 'where datefield = '19990104' )

Arjan
0
TahirKhalilCommented:
For the same date changes you can use the following query. My previous update statement was wrong because it would minus one day from the date. But you can use this Update syntax.


Update Table_Name
Set Date_Field = DateAdd(day, 1, DateAdd(MI, -1, Date_Field))
Where Date_Field = 'Jan 4 99'

/* Any Date You Wanna Change */

This will work!

TahirKhalil
0
kanthikumarpCommented:
how to decrease the other fields(month,year,hour,min,sec) in the datetime?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.