CODEWITHSUNDEEP
c#asp.netsql
Hakkı
Hakkı

Reputation: 840

select rows between two dates

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?

Upvotes: 1

Views: 295

Answers (3)

T.S.
T.S.

Reputation: 19384

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

Upvotes: 0

Mark Sowul
Mark Sowul

Reputation: 10610

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?

Upvotes: 3

Maryam Arshi
Maryam Arshi

Reputation: 2024

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)

Upvotes: 0

Related Questions