Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-SQL between

Answered T-SQL between

  • Friday, February 08, 2013 8:17 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    declare @startdate datetime='2013-02-07 09:45:54.000',
		@enddate datetime ='2013-02-08 06:45:35.000'
 select convert(date,@StartDate) as RowDateSt, convert(date, @EndDate) as RowDateEd,
 substring(convert(varchar,@startdate,120),12,2)+substring(convert(varchar,@startdate,120),15,2),
 substring(convert(varchar,@enddate,120),12,2)+substring(convert(varchar,@enddate,120),15,2)
 go

    I have this code but I have problem with the time range 0945 and 0645. Actually, I am using BETWEEN 0945 and 0645. Which doesnt work in SQL as shown

     SELECT  COUNT(*) FROM CMS.hagent where row_date between '2013-02-07'and '2013-02-08'
  and StartTime between 0945 and  0645

    But I want counts from table in this range. How would I avoid this? Now the varibales are hard coded but in actual they will be getdate().

    Moreover, in table my row date values are like '2012-09-23' data type as date, startTime like 1400, 1330 data type int.

    Thanks,

    Thanks, hsbal

     

All Replies

  • Friday, February 08, 2013 8:28 PM
     
     Answered Has Code
    Sign In to Vote
    0

    You need to put the row_date and StartTime together:

    Declare @tvTable Table (
	Col1 varchar(10)
	,row_date date
	,StartTime int
)

Declare	@StartDate	datetime
		,@EndDate	datetime

Select	@StartDate = '20130207 09:45:00'
		,@EndDate = '20130208 06:45:00'

Insert	@tvTable
Values	('Something', Cast(DateAdd(d,-1,CURRENT_TIMESTAMP) As Date), 1430)
		,('Foo', '20130207', 1945)
		,('Bar', '20130208', 1645)

Select	*
		,row_date + Cast(Stuff(Right('0000' + Cast(StartTime As varchar(4)), 4), 3, 0, ':') As datetime)
From	@tvTable
Where	row_date + Cast(Stuff(Right('0000' + Cast(StartTime As varchar(4)), 4), 3, 0, ':') As datetime) Between @StartDate And @EndDate