Help between dates

  • Hi to all,

    Imagine that i have a table with the id of an ad, and i have datein (information about the day the ad was online) and i have dateout (information about the day the ad was offline), this 2 fields are datetime...

    Now i need to know which ads were online in month x and year y.. this two parameters are type INT...

    Which is the best way to do this?

    Someone can help me?

  • Very carefully.

    Actually, it would help us if you would provide us with the table definition(s) (CREATE TABLE statement(s)), sample date (a series of INSERT INTO statements) for the table(s), expected results based on the sample data provided, and most importantly what you have done so far to solve your problem.

  • Create either a permanent or inline calendar table and join to that.

    Here's an example using a Numbers table to generate an inline calendar. I've got an add that ran from Jan through Mar, and I want to see what was active in February.

    create table #Ads (

    ID int identity primary key,

    FromDate datetime,

    ToDate datetime);

    insert into #Ads (FromDate, ToDate)

    select '1/1/2009', '3/1/2009';

    ;with InLineCalendar (Date) as

    (select dateadd(day, Number, '2/1/2009') -- All the dates for February

    from dbo.Numbers

    where Number between 0 and 27)

    select *

    from #Ads

    where exists

    (select *

    from InLineCalendar

    where Date between FromDate and ToDate);

    Does that help?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hello,

    I forgot...

    CREATE TABLE Test (Ad_ID INT, DateIn DATETIME, DateOut DATETIME)

    INSERT INTO Test

    SELECT 1,'2009-05-26 00:00:00.000' ,'2009-05-28 00:00:00.000'

    UNION

    SELECT 2,'2008-06-26 00:00:00.000' ,'2009-12-28 00:00:00.000'

    UNION

    SELECT 3,'2009-03-12 00:00:00.000' ,'2009-12-28 00:00:00.000'

    UNION

    SELECT 4,'2009-10-26 00:00:00.000' ,'2009-10-28 00:00:00.000'

    UNION

    SELECT 5,'2009-09-26 00:00:00.000' ,'2009-12-28 00:00:00.000'

    DECLARE @Month INT

    DECLARE @Year INT

    SET @Month = 10

    SET @Year = 2009

    select Distinct Ad_ID from test

    WHERE (

    ( MONTH(Datein) = @Month AND YEAR(Datein) = @Year)

    OR ( MONTH(DateOut) = @Month AND YEAR(DateOut) = @Year)

    )

    OR /*This part is not working correctly cause of the year*/

    (MONTH(DateIn) < @Month AND YEAR(DateIn) = @Year

    AND MONTH(DateOut) > @Month AND YEAR(DateOut) = @Year)

  • You should use variable with datetime type. It makes it a lot easier than dealing with the separate parts of a date value. It also helps performance since you don't aplly any function to the column you query against.

    I used some basic math to get a number like 20091001 and converted that into a character type. The variable dayStart is datetime and SQL Server will perform an implicit conversion of '20091001' into a datetime value of #2009-10-01 00:00:00.000#

    DECLARE @dayStart AS datetime

    SET @dayStart=cast(@Year*10000+@Month*100+1 AS char(8))

    select Distinct Ad_ID from @test-2

    WHERE datein<dateadd(m,1,@dayStart) and dateout>=@dayStart

    Edit: had to change the datein comparison to be < instead of <=



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi guys,

    Thanks to all 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply