?
Solved

Setting the correct parameter dateformat in a stored procedure

Posted on 2005-04-13
32
Medium Priority
?
231 Views
Last Modified: 2008-01-09
I have created a query, as below

@surname varchar(150), @enddate datetime
AS
SELECT     job.jobid, job.estimate, job.location1 + job.location2 + job.location3 + job.location4 + job.location5 AS Location, newgang.name,
                      company.companyname, gangmember.name AS surname, complete.jobcomplete
FROM         allocation INNER JOIN
                      vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.lastallocation INNER JOIN
                      complete INNER JOIN
                      job ON complete.jobid = job.jobid ON vw_job_lastliveallocation.jobid = job.jobid INNER JOIN
                      newgang ON allocation.newgangid = newgang.newgangid INNER JOIN
                      gangmember ON allocation.gangmemberid = gangmember.gangmemberid INNER JOIN
                      company ON newgang.companyid = company.companyid
WHERE gangmember.name like (@surname) and complete.jobcomplete >= @enddate

the field complete.jobcomplete is datetime field with format dd/mm/yyyy hh:mm:ss, however when my parameter @enddate looks at it, it treats it like mm/dd/yyyy hh:mm:ss and brings up the wrong date. H=I want the user to be able to input the date only in dd/mm/yyyy discarding the time part of the parameter and look for the right date format in the field

Thanks

Mark
0
Comment
Question by:polynominal
  • 14
  • 13
  • 4
  • +1
32 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13773773
Try setting the date format before calling the stored procedure

SET DATEFORMAT dmy
EXECUTE YourStoredProcedure

Or you can set it inside your stored procedure.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13773801
To discard the time portion of the input, try this:

WHERE gangmember.name like (@surname) and complete.jobcomplete >= CAST(CONVERT(VARCHAR(10), @enddate, 111) AS DATETIME)
0
 

Author Comment

by:polynominal
ID: 13773807
Thanks for the answer, much appreciated, I am just learning sql, how would I do what you suggested.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13773849
Since your @enddate is already defined as datetime, there is no use of setting the date format inside your stored procedure.  It has to be done outside the stored procedure before being called as such:

SET DATEFORMAT dmy
EXECUTE YourStoredProcedure 'Francisco', '02/01/2005'

Since you've set the date format to dmy, this date will be treated as January 2, 2005 instead of February 1, 2005.
0
 

Expert Comment

by:miggie_ra
ID: 13773874
rafrancisco is correct but i think that you want to use CAST(CONVERT(VARCHAR(10), @enddate, 103) AS DATETIME) to get the format you want instead of '111'
0
 

Author Comment

by:polynominal
ID: 13773915
Tried what you have said but Im still getting the same results
0
 

Expert Comment

by:miggie_ra
ID: 13773966
try this:

SELECT     job.jobid, job.estimate, job.location1 + job.location2 + job.location3 + job.location4 + job.location5 AS Location, newgang.name,
                      company.companyname, gangmember.name AS surname, complete.jobcomplete
FROM         allocation INNER JOIN
                      vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.lastallocation INNER JOIN
                      complete INNER JOIN
                      job ON complete.jobid = job.jobid ON vw_job_lastliveallocation.jobid = job.jobid INNER JOIN
                      newgang ON allocation.newgangid = newgang.newgangid INNER JOIN
                      gangmember ON allocation.gangmemberid = gangmember.gangmemberid INNER JOIN
                      company ON newgang.companyid = company.companyid
WHERE gangmember.name like (@surname) and CAST(CONVERT(VARCHAR(10), complete.jobcomplete,103) AS DATETIME) >= @enddate
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13773967
I tried this and it gave me January 2, 2005 instead of February 1, 2005:

SET DATEFORMAT dmy
SELECT CAST('02/01/2005' AS DATETIME)

Did you incorporate this part:

WHERE gangmember.name like (@surname) and complete.jobcomplete >= CAST(CONVERT(VARCHAR(10), @enddate, 111) AS DATETIME)

Since it's a stored procedure, can you try printing the @enddate inside the stored procedure and see it's value.
0
 
LVL 23

Expert Comment

by:apresto
ID: 13774004
I agree all, but from what i understand from your question you do need 103 format (dd/mm/yy) as Miggie_ra mentioned
0
 

Author Comment

by:polynominal
ID: 13774007
Hi, thanks I got an error message, The conversion of a char data type to a datetime datatype resulted in an out of range datetime value
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774023
You got the error message using which codes above?
0
 

Author Comment

by:polynominal
ID: 13774039
miggie ra
0
 

Author Comment

by:polynominal
ID: 13774045
WHERE gangmember.name like (@surname) and CAST(CONVERT(VARCHAR(10), complete.jobcomplete,103) AS DATETIME) >= @enddate
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774067
This will fail because outside the stored procedure you set the default date format to dmy, but inside the stored procedure you did not set it.  It went back to mdy.  So when you converted the date to dd/mm/yy format using the convert(103) then convert it back to datetime, it used the mdy format.  This is the reason why I used 111 because this will never confuse SQL server.  Try changing the format to 111 instead of 103.
0
 

Expert Comment

by:miggie_ra
ID: 13774101
SORRY ITS

WHERE gangmember.name like (@surname) and CONVERT(VARCHAR(11),complete.jobcomplete ,103)  >= @enddate
0
 

Author Comment

by:polynominal
ID: 13774149
Hi, I have tried both, I am getting blank results when searching through the date field
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774170
Please post the latest code and how you are calling it.
0
 

Expert Comment

by:miggie_ra
ID: 13774171
TRY A SIMPLE SELECT STATEMENT FROM NORTHWIND

SELECT * FROM ORDERS WHERE CONVERT(VARCHAR(11),REQUIREDDATE,103) < GETDATE()
0
 

Author Comment

by:polynominal
ID: 13774173
The problem is I am using the stored procedure, through Crystal Reports and cannot format it outside the stored procedure. sorry should have mentioned this
0
 

Author Comment

by:polynominal
ID: 13774194
ALTER   PROCEDURE csp_checkreport
@surname varchar(150), @enddate datetime
AS
SELECT     job.jobid, job.estimate, job.location1 + job.location2 + job.location3 + job.location4 + job.location5 AS Location, newgang.name,
                      company.companyname, gangmember.name AS surname, complete.jobcomplete
FROM         allocation INNER JOIN
                      vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.lastallocation INNER JOIN
                      complete INNER JOIN
                      job ON complete.jobid = job.jobid ON vw_job_lastliveallocation.jobid = job.jobid INNER JOIN
                      newgang ON allocation.newgangid = newgang.newgangid INNER JOIN
                      gangmember ON allocation.gangmemberid = gangmember.gangmemberid INNER JOIN
                      company ON newgang.companyid = company.companyid
WHERE gangmember.name like ('@surname') and CONVERT(VARCHAR(11),complete.jobcomplete ,103)  >= @enddate
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774220
>>through Crystal Reports and cannot format it outside the stored procedure<<

Since this is the case, then the control of the date format cannot be handled by the stored procedure.  If you want the format to be in "DD/MM/YYYY", you have to force it in Crystal because the data type of @enddate is already in datetime.

Have you tried my earlier suggestion:

WHERE gangmember.name like (@surname) and complete.jobcomplete >= CAST(CONVERT(VARCHAR(10), @enddate, 111) AS DATETIME)
0
 

Author Comment

by:polynominal
ID: 13774245
Have you tried my earlier suggestion:

WHERE gangmember.name like (@surname) and complete.jobcomplete >= CAST(CONVERT(VARCHAR(10), @enddate, 111) AS DATETIME)

Yes Im getting blank results
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774267
What happens if you hard-code the date?

WHERE gangmember.name like (@surname) and complete.jobcomplete >= '01/01/2005'

Do you get the desired output?
0
 

Author Comment

by:polynominal
ID: 13774272
if i use 28/01/2005 which is what i want i have to hardcode it 01/28/2005
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774281
Try adding

SET DATEFORMAT dmy

before your select statement then hard-code it to '28/01/2005'
0
 

Author Comment

by:polynominal
ID: 13774313
still treating it like 01/28/2005
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774331
I am now confused.  That's how it is supposed to treat it right?  You should hard-code it to '28/01/2005' and it will be treated as January 28, 2005.
0
 

Author Comment

by:polynominal
ID: 13774348
SET DATEFORMAT dmy

before your select statement then hard-code it to '28/01/2005'

Done that but Im still getting no results
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13774368
Then the problem now is not with the date but with the other parameter, the @surname.  I see that you are using LIKE.  Try this one:

WHERE gangmember.name like '%' + @surname + '%' and complete.jobcomplete >= @enddate
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774371
Don't forget to hardcode the @enddate as such:

WHERE gangmember.name like '%' + @surname + '%' and complete.jobcomplete >= '28/01/2005'
0
 

Author Comment

by:polynominal
ID: 13774422
thanks for all this help by the way

Freezes my pc everytime I run the query above
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13774454
Let's try decreasing the number of records to output.  Try this one:

WHERE gangmember.name like @surname + '%' and complete.jobcomplete >= '28/01/2005'

I removed the first %.  Then try using a @surname with more than 3 characters to lessen the number of records to process.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 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