?
Solved

convert datetime (hh:mm)

Posted on 1999-11-02
8
Medium Priority
?
56,120 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:hmk
8 Comments
 
LVL 2

Accepted Solution

by:
ajith_29 earned 20 total points
ID: 2179883
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
 

Expert Comment

by:kavitag
ID: 2180219
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
 

Expert Comment

by:kavitag
ID: 2180235
Ignore the previous comment. That is good if the field is datetime format.

is it char or datetime format?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 1

Expert Comment

by:dk99
ID: 2180589
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
 

Expert Comment

by:TahirKhalil
ID: 2301366
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
 
LVL 3

Expert Comment

by:ahoor
ID: 2328487
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
 

Expert Comment

by:TahirKhalil
ID: 2331063
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
 

Expert Comment

by:kanthikumarp
ID: 10782325
how to decrease the other fields(month,year,hour,min,sec) in the datetime?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By definition, working capital is the measure a company’s efficiency and its financial health from a short-term perspective. With the implementation of GST in India on July 1, 2017, the unorganized sector has to pay GST at various levels in order to…
Disk errors can be the source of sundry problems for the Exchange server, the most common one being that the database fails to mount.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question