Solved

Age between date range query (SP)

Posted on 2016-09-15
13
45 Views
Last Modified: 2016-09-16
I have a query that should retrun the records of 'contacts' that will be a certain age within a specific date range.
Example:  Gina will be 21 years old between 01/01/2016 and 01/01/2017  since she was born on: 1995-09-01 00:00:00.000

This is my query, for some reason returns no results even though I have the data above and those are my parameters.

SELECT  b.FirstNm ,
        b.LastNm ,
        a.Relation ,
        b.ContactStatus ,
        b.Archivedcont ,
        b.DobD ,
        b.Email ,
        a.UserId ,
        b.UserId AS usercontid ,
        c.FirstNm AS MainFirstNm ,
        c.LastNm AS MainLastNm
FROM    Users b
        LEFT JOIN Partiescont AS a ON a.UserId = b.UserId
        LEFT JOIN Users AS c ON c.UserId = a.PartcontId
WHERE   21 BETWEEN DATEDIFF(YEAR, b.DobD, 01 / 01 / 2016)
           AND     DATEDIFF(YEAR, b.DobD, 01 / 01 / 2017);

Open in new window


Any ideas ?
0
Comment
Question by:amucinobluedot
  • 7
  • 6
13 Comments
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41800089
SQL Server has native dates but not a way to express them in expressions. You need delimiters:

WHERE   21 BETWEEN DATEDIFF(YEAR, b.DobD, '01/01/2016')
           AND     DATEDIFF(YEAR, b.DobD, '01/01/2017')

Open in new window

0
 
LVL 11

Accepted Solution

by:
CraigYellick earned 500 total points
ID: 41800105
You might wonder why you didn't get an error message... SQL Server performs all sorts of automatic data type conversions and in this case what happened is it did the math, dividing 01 by 01 then 2016, and converted the result to a date.

Try it, most people are surprised that the result is Jan 1, 1900.

select cast(01/01/2016 as datetime)

Open in new window


Behind the scenes, SQL Server treats dates as numbers. Below, is Oct 20, 1933 at 4:16 pm.

select cast(12345.678 as datetime)

Open in new window


That's the date that's 12,345 days past 1-Jan-1900 and 4:16 pm is about 67.8% of 24 hours.
0
 

Author Comment

by:amucinobluedot
ID: 41800136
The issue seems was the delimeters, thanks so much!
0
 

Author Comment

by:amucinobluedot
ID: 41800139
Actually talked too early, the sql ran fine but my SP didn't.

Here it is:

USE [BlueDot]
GO
/****** Object:  StoredProcedure [dbo].[report_basic_contacts_04_results]    Script Date: 9/15/2016 11:38:03 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO



ALTER PROCEDURE [dbo].[report_basic_contacts_04_results]
    @Firmid INT ,
    @ContactStatus INT,
    @Archivedcont INT ,
    @Age INT ,	
    @Dobstart DATE ,
    @Dobend DATE
AS
		SELECT b.FirstNm ,
        b.LastNm ,
        a.Relation ,
        b.ContactStatus ,
        b.Archivedcont ,
        b.DobD ,
        b.Email ,
        a.UserId ,
        b.UserId AS usercontid ,
        c.FirstNm AS MainFirstNm ,
        c.LastNm AS MainLastNm
 FROM   Users b
        LEFT JOIN Partiescont AS a ON a.UserId = b.UserId
        LEFT JOIN Users AS c ON c.UserId = a.PartcontId
    WHERE   b.FirmId = @Firmid
            AND b.ContactStatus LIKE @ContactStatus
            AND b.Archivedcont LIKE @Archivedcont
            AND b.DobD IS NOT NULL
            AND @Age BETWEEN DATEDIFF(YEAR, b.DobD, '@Dobstart')
                     AND     DATEDIFF(YEAR, b.DobD, '@Dobend');

Open in new window


I got the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

/bluedot/Intranet/reports/basic_contacts_04_results.asp, line 53

What is strange is that I don't use the delimiters for other queries and those work fine :$
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41800151
The variables cannot be in quotes. SQL Server will perform the data conversion, if needed, when you pass the parameter.

@Dobstart DATE

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 41800188
This is the ASP code that passes the data to the page. Is it incorrect ?

<%

Dim sp_reporting__Firmid
sp_reporting__Firmid = "0"
if(Session("Firmid")     <> "") then sp_reporting__Firmid = Session("Firmid")    

Dim sp_reporting__Archivedcont
sp_reporting__Archivedcont = ""
if(request.form("archivedcont") <> "") then sp_reporting__Archivedcont = request.form("archivedcont")

Dim sp_reporting__ContactStatus
sp_reporting__ContactStatus = ""
if(request.form("contactstatus") <> "") then sp_reporting__ContactStatus = request.form("contactstatus")

Dim sp_reporting__Age
sp_reporting__Age = "0"
if(request.form("age")     <> "") then sp_reporting__Age = request.form("age")    

Dim sp_reporting__Dobstart
sp_reporting__Dobstart = "01/01/1900"
if(request.form("age_start")      <> "") then sp_reporting__Dobstart = request.form("age_start")     

Dim sp_reporting__Dobend
sp_reporting__Dobend = "01/01/2050"
if(request.form("age_end")     <> "") then sp_reporting__Dobend = request.form("age_end")    

%>
<%

set sp_reporting = Server.CreateObject("ADODB.Command")
sp_reporting.ActiveConnection = MM_bluedot_STRING
sp_reporting.CommandText = "dbo.report_basic_contacts_04_results"
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@RETURN_VALUE", 3, 4)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@Firmid", 3, 1,4,sp_reporting__Firmid)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@Archivedcont", 200, 1,5000,sp_reporting__Archivedcont)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@ContactStatus", 200, 1,5000,sp_reporting__ContactStatus)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@Age", 200, 1,4,sp_reporting__Age)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@Dobstart", 135, 1,10,sp_reporting__Dobstart)
sp_reporting.Parameters.Append sp_reporting.CreateParameter("@Dobend", 135, 1,10,sp_reporting__Dobend)
sp_reporting.CommandType = 4
sp_reporting.CommandTimeout = 0
sp_reporting.Prepared = true
set report_results = sp_reporting.Execute
report_results_numRows = 0

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:CraigYellick
ID: 41800235
Using mystery constants like 135 in your code makes it hard to debug and maintain. I had to look it up, and 135 is adDBTimeStamp. Despite time" in the name, it's not really intended to be used for actual dates and times. You should be using adDBDate  (133).

Looks like the date-related variables end up as strings. When you assign them as values they should end up converted to dates.

The quotes around the variable names in the SQL procedure would produce the error you're seeing. After removing those and changing the parameter data types in your asp.net code, it ought to work.
0
 

Author Comment

by:amucinobluedot
ID: 41800320
I am using classic ASP. I changed it to 133 and I got this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[Microsoft][ODBC SQL Server Driver]Optional feature not implemented

/bluedot/Intranet/reports/basic_contacts_04_results.asp, line 53
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41800344
Hmm. Maybe the 135 constant is correct for dates when using that driver. It's been a long time since I've tangled with ODBC and ASP classic. You probably have other places where dates are being successfully passed to SQL Server, use whichever constant works there. Could be the 135 was correct after all.
0
 

Author Comment

by:amucinobluedot
ID: 41800353
Yes. Still I get no results
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41800384
First thing to check is whether or not you can call the stored procedure directly from SQL Server Management Studio or whatever tool you have handy. Pass parameters that you know should have results. If they are not displayed then there's still a problem with the procedure.

If the procedure works, then use the same parameter values and add them as constants in your ASP code. That is, don't use variables, make the parameter value assignments directly in code. That way you know you're not have some kind of type conversion problem.

Finally, do you have other ASP code that is successfully calling stored procedures? Might be something going on with all those parameters being passed to the CreateParameter method.
0
 

Author Comment

by:amucinobluedot
ID: 41800387
I'll run the sp first. And post results. Thx.
0
 

Author Comment

by:amucinobluedot
ID: 41801184
Turns out the SP was correct, the order in which the parameters were passed to the SP were incorrect even though the names are correct ... sigh! case closed.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now