Solved

query between separate date fields

Posted on 2013-01-30
6
265 Views
Last Modified: 2013-01-30
i need to query between two datetime columns. Start date is given and need to calculate 90 days out from that date.

declare @StartDate as date, @EndDate as date
set @StartDate = '1/14/2013'
set @EndDate = dateadd(d,90,@StartDate)

query
where
cast(StartTimeLocal as Date) >= @StartDate
and cast(EndTimeLocal as Date) <=  @EndDate


problem is that i don't see all the rows. if i comment out: and cast(EndTimeLocal as Date) <=  @EndDate I see the rows.

example:
start date of 2013-01-14 06:00:00.000
end date of 2013-08-02 00:59:00.000

this row only shows when i comment out: and cast(EndTimeLocal as Date) <=  @EndDate otherwise it doesn't show.

how do i find the rows between those dates?
0
Comment
Question by:fwstealer
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:Nalinkumarbalaji
ID: 38836014
Use the below one for date filtering

Convert(Varchar,StartTimeLocal,101) >= @StartDate
0
 

Author Comment

by:fwstealer
ID: 38836033
are you saying

declare @StartDate as date, @EndDate as date
set @StartDate = '1/14/2013'
set @EndDate = dateadd(d,90,@StartDate)

select StartTimeLocal, EndTimeLocal from Schedules
where
Convert(Varchar,StartTimeLocal,101) >= @StartDate
and
 Convert(Varchar,StartTimeLocal,101) <= @EndDate
0
 

Author Comment

by:fwstealer
ID: 38836045
this still didn't show the row I'm looking for

select StartTimeLocal, EndTimeLocal from Schedules
where
Convert(Varchar,StartTimeLocal,101) >= @StartDate
and
 Convert(Varchar,EndTimeLocal,101) <= @EndDate
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:Nalinkumarbalaji
ID: 38836048
Use this below one

declare @StartDate as datetime, @EndDate as datetime
set @StartDate = '1/14/2013'
set @EndDate = dateadd(d,90,@StartDate)

select StartTimeLocal, EndTimeLocal from Schedules
where
Convert(Varchar,StartTimeLocal,101) >= @StartDate
and
 Convert(Varchar,StartTimeLocal,101) <= @EndDate
0
 

Author Comment

by:fwstealer
ID: 38836102
that shows the row I need but also shows beyond 90 days

declare @StartDate as datetime, @EndDate as datetime
set @StartDate = '1/14/2013'
set @EndDate = dateadd(d,90,@StartDate)

select @StartDate, @EndDate
--startdate = 2013-01-14
--enddate = 2013-04-14

this query goes way beyond the end date by showing data in the month of august:
select StartTimeLocal, EndTimeLocal from Schedules
where
Convert(Varchar,StartTimeLocal,101) >= @StartDate
and
 Convert(Varchar,StartTimeLocal,101) <= @EndDate

i just want to show all the rows between startdate and enddate not beyond the enddate
0
 
LVL 3

Accepted Solution

by:
Nalinkumarbalaji earned 500 total points
ID: 38836134
Run the below one and check

declare @StartDate as datetime, @EndDate as datetime
set @StartDate = '1/14/2013'
set @EndDate = Convert(Varchar,dateadd(d,90,@StartDate),101)

select StartTimeLocal, EndTimeLocal from Schedules
where
Convert(Varchar,StartTimeLocal,101) >= @StartDate
and
 Convert(Varchar,StartTimeLocal,101) <= @EndDate
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL 2008 R2 syntax 11 35
SQL Server tables join on parse list 6 33
SQL Server 2012 r2 - Sum totals 2 28
Query group by data in SQL Server - cursor? 3 47
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question