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

by:Brendt Hess
Brendt Hess 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.

Technology Partners: 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

717 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