TSequel Between on Dates

Posted on 2012-04-05
Last Modified: 2012-04-06
Hi everyone,

I need some Tsequel Coding that will find some records between two dates so that I can do a Count.  I need the coding where Between is used.

SELECT     COUNT(dbo.LicenseFY10.LicType) AS TotalCount, dbo.luLicenseType.LicType, dbo.luLicenseType.LicDesc
FROM         dbo.LicenseFY10 INNER JOIN
                      dbo.luLicenseType ON dbo.LicenseFY10.LicType = dbo.luLicenseType.LicType
WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate < '09/30/2011')
GROUP BY dbo.luLicenseType.LicType, dbo.luLicenseType.LicDesc

This works but does not give me the real records between the two dates.

How can I re-write this to do the count correctly.

I need the count of Lictypes between, 10/01/2010 and 09/30.2011.

Question by:jjc9809
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 11

Assisted Solution

by:Simone B
Simone B earned 334 total points
ID: 37812736
WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate between 'oct 1, 2010' and 'sep 30, 2011')
LVL 32

Assisted Solution

bhess1 earned 166 total points
ID: 37812840
Two quick questions:

1)  Do you need the data through 9/30, or up to but not including 9/30

2)  Does tour ExpirationDate field contain a time part as well.

Your WHERE clauses vary in these different cases.  If you have no time portion in the fields, and you need it inclusive of 9/30 (as I assume), then Buttercup1 has your answer (although I would format the dates as yyyymmdd instead of as they are formatted in his example - yyyymmdd is a universally accepted date format, as opposed to American (mm/dd/yyyy), Europe and most everywhere else (dd/mm/yyyy), or dates including month names (which vary by language).  The BETWEEN clause in this case would be:

BETWEEN '20101010' and '20110930'

If the fields include a time portion that is used, then you need to use something other than BETWEEN.  Some people add the max time to the text string, but I find it cleaner and easier to use:

dbo.LicenseFY10.ExpirationDate >=  '20101001' and dbo.LicenseFY10.ExpirationDate < '20101001'

Any date/time from the start of October 1, 2010 to the end of Sep 30, 2011, would be included.

Author Comment

ID: 37812913

There is no time part on the issue date or expiration date.  The dates are in this format 10/01/2010 to 09/30/2011.

I need the data thorugh 9/30/2011.  I need all records where a License was issued from 10/01/2010 to 09/30/2011.  Then I can take a count of the License Number.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Expert Comment

by:Simone B
ID: 37812932
Change your Where clause to use the BETWEEN operator. You can use whatever format you like for the dates, and SQL will interpret it.

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate between '10/01/2010' and '09/30/2011')

Author Comment

ID: 37813101
Hello everyone,

I do have a time part on the issue date and expiration date after looking.  So Between will not work.

LVL 11

Expert Comment

by:Simone B
ID: 37813190
WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.ExpirationDate between '10/01/2010 00:00:01' and '09/30/2011 11:59:59')
LVL 11

Accepted Solution

Simone B earned 334 total points
ID: 37813208
Specifying the time means that you run the risk of losing 2 seconds of data. Not specifying the time will give you everything from both the beginning and ending dates and everything between, inclusive. It doesn't matter that your data type includes the time, you can still use "between" with dates alone.

Author Comment

ID: 37813598

That does makes sense what you said.   I am just confused not using the Issue Date in the query.  The Issue Date is always 10/01/2010 for the year though so I see where using just Expiration date would pull the number needed.

Am I right in assuming this?

LVL 11

Expert Comment

by:Simone B
ID: 37813894
I'm sorry, I missed that. So to clarify: do you want to return all lictype of 010 and 011, issued on Oct 1, 2010? If so then your where clause would look like this:

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate ='10/01/2010' )

If you also want to filter on the expiration date, you can add that as well, either using between or not, as you choose:

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate ='10/01/2010' ) and (dbo.LicenseFY10.ExpirationDate between '10/01/2010'  and '09/30/2011')

or this:

WHERE     (dbo.LicenseFY10.LicType IN ('010', '011')) AND (dbo.LicenseFY10.IssueDate ='10/01/2010' ) and (dbo.LicenseFY10.ExpirationDate <= '09/30/2011')

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 94
Two tables - Sum of values - What is the difference 31 59
Replace Dates in query 14 56
Backing up Large SQL Server VM Best practice [using Veeam Backup] 8 62
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question