• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

SQL 2000 - Query help

SQL 2000 - I need to write a update query that will take the input date time value:


1. If the DateTime value is between 12.00 AM to 3.00 AM then set the date to previous day with time 12.59.59 PM.

how to write an Update query selecting from another query?

Soemthing like this:
update Table1
select datetime from Table1

Thanks
0
mani_sai
Asked:
mani_sai
  • 4
  • 2
  • 2
  • +2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
update <table>
set dateTimer = dateadd(d, -1, dateTimer)
where
datepart(hh,datetimer) between 0 and 2 or
(datepart(hh,datetimer) = 3 and datepart(mi, datetimer) = 0)
0
 
ravikantninaveCommented:
update TestTable set mdate = (Select Case When DATEPART(HOUR, AppMstDOJ ) >=0 and DATEPART(HOUR, AppMstDOJ ) <=3 then CAST(CAST(CONVERT(DATE, AppMstDOJ ,101) AS VARCHAR) + ' 12' + ':' +  '59' + ':' + '59' AS DATETIME) else AppMstDOJ  end from AppMast where AppMstID = 272)

Open in new window

0
 
mani_saiAuthor Commented:
Thanks.

Date is moved to previous date , I also want Time portion to be set 23:59:59...

any Idea?

Thanks
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
update <table>
set dateTimer = cast(LEFT(CONVERT(nvarchar, DATEADD(DAY, -1, dateTimer), 120), 11) + '23:59:59' as datetime)where
datepart(hh,datetimer) between 0 and 2 or
(datepart(hh,datetimer) = 3 and datepart(mi, datetimer) = 0)

If you already ran the first update just don't do the dateadd again:

update <table>
set dateTimer = cast(LEFT(CONVERT(nvarchar, dateTimer, 120), 11) + '23:59:59' as datetime)where
datepart(hh,datetimer) between 0 and 2 or
(datepart(hh,datetimer) = 3 and datepart(mi, datetimer) = 0)
0
 
ravikantninaveCommented:
update TestTable set mdate = (Select Case When DATEPART(HOUR, AppMstDOJ ) >=0 and DATEPART(HOUR, AppMstDOJ ) <=3 then CAST(CAST(CONVERT(DATE, DATEADD(DAY, -1, AppMstDOJ ),101) AS VARCHAR) + ' 23' + ':' +  '59' + ':' + '59' AS DATETIME) else AppMstDOJ  end from AppMast where AppMstID = 272)

Open in new window

0
 
John_VidmarCommented:
update Table1
set datetimefield = convert(varchar,dateadd(day,-1,datetimefield), 101) + ' 12:59:59 PM'
where convert(varchar,datetimefield,24) between '00:00:00' and '03:00:00'

Open in new window

0
 
UnifiedISCommented:
23:59.59 is easer than 12:59.59
Is it really 3 AM  or < 3 AM?

Just strip the time and subtract one second
UPDATE table
SEt yourdtm = DATEADD(second, -1, CONVERT(varchar(10), yourdtm, 101))
WHERE DATEPART(Hour, yourdtm) BETWEEN 0 AND 2
OR
DATEPART(Hour, yourdtm) = 3 AND DATEPART(minute, yourtdtm) = 0
0
 
mani_saiAuthor Commented:
Kyle Abrahams:

Further down my calculation...

I want to strip off the  time portion and update the same date column with just date...



Thanks to everyone for the suggestions.
0
 
mani_saiAuthor Commented:
I want to do update for the remaining records...

other than

where
datepart(hh,datetimer) between 0 and 2 or
(datepart(hh,datetimer) = 3 and datepart(mi, datetimer) = 0)
0
 
mani_saiAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now