select rows between two dates

Question

I'm trying to do announcement page with asp.net c# and sql server.

In this page i have "popup" field in datebase and if this field checked I want to show this anouncement code in tinybox like this.

https://i.sstatic.net/dj4Km.png

Its working but I have a problem with dates.

I want to list last "popup" checked field and if this field between the two dates. My sql query looks like this:

    Select Top 1 * 
    From duyurlarx 
    Where ([popup] = 1) 
        And tarih 
            BETWEEN convert(date, getdate()) 
                AND DATEADD(day,popupsure,convert(date, getdate()))

Field explanation:

tarih = date field
popupsure = day count for popup show

This means if "tarih" field = 01.02.2014 and "popupsure" field = 3, then this announcement will be shown until 04.02.2014.

However, when I change the date field to 1 or 2 days before, it's not working. Am I doing something wrong?

Mark Sowul · Accepted Answer · 2014-01-31 21:25:15Z

3

Your query says "Give me all the [duyurlarx] with [tarih] between today and [popupsure] days from now" |TODAY ---- TARIH ---- TODAY + POPUPSURE|

It sounds like you want the reverse: all [duyurlarx] where today is between [tarih] and [tarih + popupsure]

|TARIH ---- TODAY ---- TARIH + POPUPSURE|

That would correspond to

Select Top 1 * 
From duyurlarx 
Where ([popup] = 1) 
    And convert(date, getdate()) 
        BETWEEN tarih 
            AND DATEADD(day, popupsure, tarih)

Does that sound right?

answered Jan 31, 2014 at 21:25

Mark Sowul

10.7k1 gold badge49 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Sowul Over a year ago

Also, consider adding a computed column on duyurlarx for DATEADD(DAY, popupsure, tarih)

Maryam Arshi · Accepted Answer · 2014-01-31 21:23:04Z

0

You should Cast your fields as Date to have a reliable Date comparison.

Select Top 1 * 
    From duyurlarx 
    Where ([popup] = 1) 
        And cast(tarih as Date)
            BETWEEN Cast(GetDate() as Date) 
                AND Cast(DATEADD(day,popupsure,convert(date, getdate())) as Date)

answered Jan 31, 2014 at 21:23

Maryam Arshi

2,0342 gold badges20 silver badges34 bronze badges

3 Comments

Maryam Arshi Over a year ago

GetDate() returns DateTime, I think

Josh Darnell Over a year ago

Isn't he already doing that, essentially? With convert(date, getdate())?

Mark Sowul Over a year ago

@RickS that is dangerously wrong after midnight GETDATE() != '2014-01-31'. Maryam is correct. This was a colossal pain before SQL 2008 introduced the DATE type

T.S. · Accepted Answer · 2014-01-31 21:36:10Z

0

This is another variant that WILL work

Select Top 1 * 
From duyurlarx 
Where ([popup] = 1) 
    And cast(getdate() as date) 
        BETWEEN cast(tarih as date) AND cast(tarih + popupsure as date)

You want CAST AS DATE because you don't want minutes. And you want to check if today IS between tarih and tarih + popupsure

The single day is added by default when you say dateField + 1

edited Jan 31, 2014 at 21:36

answered Jan 31, 2014 at 21:29

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

5 Comments

Mark Sowul Over a year ago

The fact that you have to explain that "The single day is added by default when you say dateField + 1" demonstrates that it is less clear than DATEADD(DAY, [num], [date])

T.S. Over a year ago

It is less clear only for those who doesn't know how db server works and, in fact, this is a good item for learning. I can also point that CAST to DATE is faster than convert

T.S. Over a year ago

@İsmailHakkıŞen I understand. It is good answer and will work most of the time but it leaves window for incorrect behavior. My answer closes this window

Mark Sowul Over a year ago

The advantage of CAST is that it is ANSI compliant. CAST is implemented as CONVERT: you can see this if you view the execution plan. Please offer proof that it is faster.

T.S. Over a year ago

@MarkSowul I was recently benchmarking date comparisons in different formats. CAST was outperforming in SQL server.

Collectives™ on Stack Overflow

select rows between two dates

3 Answers 3

1 Comment

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related