Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare date? Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 11:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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
Post #1443093
Posted Wednesday, April 17, 2013 12:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 218, Visits: 1,858
Are you trying to compare an actual date or just a time ?
Can you put together some sample data ?
Post #1443110
Posted Wednesday, April 17, 2013 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1443132
Posted Wednesday, April 17, 2013 1:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:05 AM
Points: 41, Visits: 85
table structure :
CREATE TABLE [dbo].[Orders](
[Id] [int] NULL,
[DateofOrd] [datetime] NULL
)
Table data:
Id DateofOrd
1 2013-04-17 13:00:41.847
2 2013-04-17 14:00:41.847
3 2013-04-17 15:00:41.847
4 2013-04-17 15:10:41.847
5 2013-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
2 2:00PM
3 3:00PM
4 3:10PM
Post #1443134
Posted Wednesday, April 17, 2013 2:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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 EndTime Flag
1 2013-04-03 00:00:00.000 8:00AM 8:15AM 0
1 2013-04-03 00:00:00.000 8:15AM 8:30AM 0
1 2013-04-03 00:00:00.000 8:30AM 8:45AM 0
1 2013-04-03 00:00:00.000 8:45AM 9:00AM 0
1 2013-04-03 00:00:00.000 9:00AM 9:15AM 0
1 2013-04-03 00:00:00.000 9:15AM 9:30AM 0
1 2013-04-03 00:00:00.000 9:30AM 9:45AM 0
1 2013-04-03 00:00:00.000 9:45AM 10:00AM 0
1 2013-04-03 00:00:00.000 10:00AM 10:15AM 0
1 2013-04-03 00:00:00.000 10:15AM 10:30AM 0
1 2013-04-03 00:00:00.000 10:30AM 10:45AM 0
1 2013-04-03 00:00:00.000 10:45AM 11:00AM 0
1 2013-04-03 00:00:00.000 11:00AM 11:15AM 0
1 2013-04-03 00:00:00.000 11:15AM 11:30AM 0

1 2013-04-02 00:00:00.000 1:00PM 1:15PM 0
1 2013-04-02 00:00:00.000 12:45PM 1:00PM 0
1 2013-04-02 00:00:00.000 12:30PM 12:45PM 0
1 2013-04-02 00:00:00.000 12:15PM 12:30PM 0
1 2013-04-02 00:00:00.000 8:00AM 8:15AM 0

thanks
Abhas.
Post #1443135
Posted Wednesday, April 17, 2013 2:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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.
Post #1443137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse