hmk
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ignore the previous comment. That is good if the field is datetime format.
is it char or datetime format?
is it char or datetime format?
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
and datepart(mi,date_field)=0
update table_name
set
date_field = dateadd(mi,-1,date_field)
where
convert(char(8),date_field
and datepart(mi,date_field)=0
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 */
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 */
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(c
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
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(c
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
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
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
how to decrease the other fields(month,year,hour,min
set date_field = dateadd(mi,-1,dateadd(dd,1
where date_field = '1/4/99'
when hh and mi are not specified it assumes (12:00 AM)midnight time.