April 16, 2013 at 11:03 pm
Hi All,
Is it possible to copare dete in format 10:30 AM.
for example i want to find out the date which is in between 10:30 AM to 11:30 AM.
Thanks
April 17, 2013 at 12:28 am
Are you trying to compare an actual date or just a time ?
Can you put together some sample data ?
April 17, 2013 at 1:39 am
What data type did you define the column you are refering to ?
/* Example using DateTime data type */
Select DATEADD(HH, datediff(HH, 0, getdate()) - 1, 0) FromHH
, DATEADD(HH, datediff(HH, 0, getdate()), 0) ToHH
, DateTimeLastRun
from yourschema.YourTableOrView
where DateTimeLastRun>= DATEADD(HH, datediff(HH, 0, getdate()) - 1, 0)
and DateTimeLastRun< DATEADD(HH, datediff(HH, 0, getdate()), 0)
order by DateTimeLastRun ;
/* Example using Time data type */
Create table #ssc ( idnr int identity(1,1) not null primary key, Dt date not null default getdate() , Tm time not null default getdate() ) ;
go
insert into #ssc default values
go 50
select *
from #ssc
where Tm >= '09:36:18'
and Tm < '09:36:25'
order by Dt
, Tm ;
select *
from #ssc
where Tm >= '9:36 AM'
and Tm < '9:37 AM'
order by Dt
, Tm ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 17, 2013 at 1:54 am
table structure :
CREATE TABLE [dbo].[Orders](
[Id] [int] NULL,
[DateofOrd] [datetime] NULL
)
Table data:
Id DateofOrd
12013-04-17 13:00:41.847
22013-04-17 14:00:41.847
32013-04-17 15:00:41.847
42013-04-17 15:10:41.847
52013-04-17 16:10:41.847
;WITH ordercte(id,timevalue)as
(
select id,ltrim(right(convert(nvarchar(20),DateofOrd,0),8)) as timevalue from orders
)
select * from ordercte where timevalue between '2:00PM' AND '3:10PM'
Result:
Id Timevalue
22:00PM
33:00PM
43:10PM
--chalam
April 17, 2013 at 2:00 am
Hi,
I am having 5 columns as below.
Now I want to update selected data only depend upon lunch time. for example if lunch time of StuID 1 is between 1AM to 11AM then i want to set flag = 1.thus it will update 4 rows.
StuID EffectiveDate StartTime EndTimeFlag
12013-04-03 00:00:00.000 8:00AM 8:15AM0
12013-04-03 00:00:00.000 8:15AM 8:30AM0
12013-04-03 00:00:00.000 8:30AM 8:45AM0
12013-04-03 00:00:00.000 8:45AM 9:00AM0
12013-04-03 00:00:00.000 9:00AM 9:15AM0
12013-04-03 00:00:00.000 9:15AM 9:30AM0
12013-04-03 00:00:00.000 9:30AM 9:45AM0
12013-04-03 00:00:00.000 9:45AM10:00AM0
12013-04-03 00:00:00.00010:00AM10:15AM0
12013-04-03 00:00:00.00010:15AM10:30AM0
12013-04-03 00:00:00.00010:30AM10:45AM0
12013-04-03 00:00:00.00010:45AM11:00AM0
12013-04-03 00:00:00.00011:00AM11:15AM0
12013-04-03 00:00:00.00011:15AM11:30AM0
12013-04-02 00:00:00.000 1:00PM 1:15PM0
12013-04-02 00:00:00.00012:45PM 1:00PM0
12013-04-02 00:00:00.00012:30PM12:45PM0
12013-04-02 00:00:00.00012:15PM12:30PM0
12013-04-02 00:00:00.000 8:00AM 8:15AM0
thanks
Abhas.
April 17, 2013 at 2:13 am
Hi Matak,
My Procedure is like
ALTER PROCEDURE [dbo].[Usp_UpdateLunchTime]
@StuID INT,
@StartDate datetime,
@EndDate datetime,
@StartTime varchar(20),
@EndTime varchar(20)
--@LunchStart varchar(20),
--@LunchEnd varchar(20)
--@DayName varchar(100),
AS
BEGIN
update tblTime set flag = 1
where StuID = @StuID and EffectiveDate =@StartDate
and StartTime between right(Convert(char(20), @StartDate,100),7) and right(Convert(char(20), @EndDate,100),7)
--CONVERT(VARCHAR(10), EffectiveDate, 110) between CONVERT(VARCHAR(10), @StartDate, 110) and CONVERT(VARCHAR(10), @EndDate, 110)
--exec Usp_UpdateLunchTime 1,'2013-04-01 00:00:00.000','2013-04-05 00:00:00.000','2013-04-01 07:00:00.000','2013-04-01 20:00:00.000'
END
Thanks
Abhas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply