How to write sql query to select different date range

I am preparing weekly reports using Excel by using data from SQL. I have table in SQL database named "Campaign" in this table I have (CampaignID, year, month, day, cost, clicks, impressions) I would like to write a query that select data between "july 27" and "August 08".  I tried using this query:
[ select * from campaign where month between '07' and '08' ]
but it gives me data within these two months but I cant specify the days..
the user usually get prompted to enter year, month and day in Excel by getting data from "other source" as "other Microsoft query"
I know you would properly think why I don't have "date" column instead that specify the exact date I tried doing that but the format of the date is  “ 2014-08-01 00:00:00.000”  and what the user enter is "01-08-2014" so varchar cannot be read as datetime

Any help how can I solve my problem!

Thank you
Rawan JambiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I would like to write a query that select data between "july 27" and "August 08".
SELECT data
FROM table_name
WHERE some_date_column BETWEEN '2014-07-27' AND '2014-08-08'

Open in new window

>and what the user enter is "01-08-2014" so varchar cannot be read as datetime
Does this mean that dates are being stored in a varchar columns?  For starters, this needs to be redesigned so that dates are in date columns, but in the meantime you can change the query to convert the varchar to a date
SELECT data
FROM table_name
WHERE CAST(some_date_column as DATE) BETWEEN '2014-07-27' AND '2014-08-08'
   AND ISDATE(some_date_column) = 1

Open in new window

Note the addition of the ISDATE, which is used to weed out any non-date values such as 'banana' that were entered into the column that cannot be converted to a date, which would otherwise cause the conversion to fail.
0
Rawan JambiAuthor Commented:
in the picture attached you can see how my table is design  I can't select date in this format (27-07-2014) and (08-08-2014).. I have to select each one of them separately as year, month and day
expert.PNG
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw, please post images using the image control, and not the attachment control, so experts can view the image without having to click around.

In that case, still convert, but do it this way..
SELECT data
FROM table_name
WHERE CAST(CAST(year as varchar(4)) + '-' + CAST(month as varchar(2)) + '-' + CAST(day as varchar(2)) as date) BETWEEN '2014-07-27' AND '2014-08-08'
   AND ISDATE(CAST(year as varchar(4) + '-' + CAST(month as varchar(2)) + '-' + CAST(day as varchar(2))) = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
Caveat: Using ISDATE() or ISNUMERIC() and an CAST() in the same query may not work. The optimizer may rearrange the physical execution and execute the CAST() before its using the result of ISDATE(). Thus it may result in an convert error.

E.g. on Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
DECLARE @table_name TABLE
    (
      data INT ,
      some_date_column NVARCHAR(255)
    );

INSERT  INTO @table_name
        ( data, some_date_column )
VALUES  ( 0, N'asdsad' ),
        ( 1, '20140811' );

DECLARE @temp TABLE
    (
      data INT ,
      some_date_column DATE
    );

INSERT  INTO @temp
        SELECT  *
        FROM    @table_name
        WHERE   ISDATE(some_date_column) = 1        

SELECT  *
FROM    @temp
WHERE   some_date_column BETWEEN '2014-07-27' AND '2014-08-08';

SELECT  data
FROM    @table_name
WHERE   CAST(some_date_column AS DATE) BETWEEN '2014-07-27' AND '2014-08-08'
        AND ISDATE(some_date_column) = 1;

Open in new window


in this case, neither a CTE nor a real subquery works. Both lead to a convert error.
0
ste5anSenior DeveloperCommented:
When using SQL Server 2012+ use the DATEFROMPARTS() function.
DECLARE @table_name TABLE
    (
      data INT ,
      some_date_column NVARCHAR(255)
    );

INSERT  INTO @table_name
        ( data, some_date_column )
VALUES  ( 0, N'asdsad' ),
        ( 1, '20140811' );

DECLARE @temp TABLE
    (
      data INT ,
      some_date_column DATE
    );

INSERT  INTO @temp
        SELECT  *
        FROM    @table_name
        WHERE   ISDATE(some_date_column) = 1        

SELECT  *
FROM    @temp
WHERE   some_date_column BETWEEN '2014-07-27' AND '2014-08-08';

SELECT  data
FROM    @table_name
WHERE   CAST(some_date_column AS DATE) BETWEEN '2014-07-27' AND '2014-08-08'
        AND ISDATE(some_date_column) = 1;

Open in new window

0
Rawan JambiAuthor Commented:
Thank you that runs successfully
0
PortletPaulfreelancerCommented:
DATETIME, DATE or TIME data types are NOT stored in a what-you-see-is-what-is-stored fashion...

That you see (by default) dates like this “2014-08-01 00:00:00.000” does not mean that you cannot compare an input in the form of "01-08-2014" to a stored date.

You would - absolutely - be better off with a date I believe. Can you add a computed column to that table perhaps?

What version of SQL Server is it?


SQL Server 2012+ allows DATEFROMPARTS() so you can easily make a date from year, month, day
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  The DATEFROMPARTS solution is somewhat simpler, but the base problem here is that it is poor design to store a date in separate year, month, day columns and that should be resolved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.