Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1367
  • Last Modified:

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
0
Rawan Jambi
Asked:
Rawan Jambi
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
PortletPaulCommented:
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now