January 13, 2015 at 9:01 am
Hi, I'm trying to set up a proc that will allow us to execute it with a data parameter but not in the std YYYY/MM/DD format. I want the user to be able to key in DD/MM/YY to execute the proc ie execute myproc '13/05/99', '14/05/99'
If I code this below it works if I run it keying the YY/MM/DD format, so how do i change it to allow the user to run it and type the DD/MM/YY format
CREATE PROC [dbo].[myproc]
(@StartDate DATETIME,
@EndDate DATETIME)
AS
select * from tableA
where transaction_date_time >= @StartDate
and transaction_date_time < @EndDate
I've tried various converts but always get 'Error converting data type varchar to datetime'
Thanks in advance!
January 13, 2015 at 9:54 am
Without more information, I have 2 possible guesses:
Your column transaction_date_time is a string that you're comparing to a datetime and that's causing the error. To correct this error, you should either correct your column data type to make it an actual date/time data type or use a non-sargable argument such as CONVERT( datetime, transaction_date_time , 3) >= @StartDate
The other possibility is that you're calling the stored procedure using strings instead of dates, you should send dates in your app code or if you're calling it with T-SQL, you should use CONVERT() with the appropiate format code.
January 13, 2015 at 10:00 am
Hi, thank-you for that. Was just typing in the fact that I'd resolved it.
I had to set the 2 variables to varchar, I had them as datetime! doh
Then I just converted the where clause ie
and s.transaction_date_time >= convert(datetime,@StartDate,3)
January 13, 2015 at 10:20 am
So the problem was when calling the stored procedure.
If you corrected your app code, you shouldn't have changed your stored procedure.
January 13, 2015 at 12:26 pm
simon.letts (1/13/2015)
Hi, thank-you for that. Was just typing in the fact that I'd resolved it.I had to set the 2 variables to varchar, I had them as datetime! doh
Then I just converted the where clause ie
and s.transaction_date_time >= convert(datetime,@StartDate,3)
Datetime is the only appropriate datatype when dealing with datetime information. If at all possible keep the datatype of your parameter as a datetime and make the appropriate changes to whatever application(s) are calling your procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 2:07 am
Thaks for that. So can you give me an example of how you would have coded it please. 🙂
January 14, 2015 at 7:23 am
simon.letts (1/14/2015)
Thaks for that. So can you give me an example of how you would have coded it please. 🙂
I would have coded it exactly as your original post. That means for datetime values you have a datetime parameter. If there are varying "formats" then the application consuming the procedure is what needs to change.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 7:41 am
But theres no app, all I'm executing is that SP via SSMS
January 14, 2015 at 7:42 am
execute myproc '13/05/99', '14/05/99'
January 14, 2015 at 7:50 am
simon.letts (1/14/2015)
execute myproc '13/05/99', '14/05/99'
Then use ANSI standard date formats.
exec myproc '1999-05-13', '1999-05-14'
Or set the dateformat in your connection.
set dateformat dmy
execute myproc '13/05/99', '14/05/99'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 8:09 am
Thanks fot that.
But as i say, using the ANSI format is what I want to get away from, it needs to be dd/mm/yy for ease of use by our business.
Also I'd rather give them as little to have to think about as possible ie just the exec statement without them thinking they have to enter a dateformat statement too. But it's useful stuff.
thanks
January 14, 2015 at 8:32 am
simon.letts (1/14/2015)
Thanks fot that.But as i say, using the ANSI format is what I want to get away from, it needs to be dd/mm/yy for ease of use by our business.
Also I'd rather give them as little to have to think about as possible ie just the exec statement without them thinking they have to enter a dateformat statement too. But it's useful stuff.
thanks
Wait a second....first you said that there is no front end that this is being executed directly in SSMS. Then you say that you have end users that want to use this. Are you saying you have end users with access to SSMS and they are executing stored procs???
If you want to make it easy for the business create a simple front end. This eliminates any thinking from them. Remember that the datetime datatype does not have a format, it is the string you are using that has a format and you need sql to decipher it. Even if you could have sql determine the proper format what would you do with dates that you can't tell (2/3/4)??? Which value is what? There is NO way you can determine that dynamically because in this case it could be any order as they would all work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 8:35 am
Yes the unit are attached to IT and support the business so they are a little more technical and use SSMS
January 14, 2015 at 8:40 am
simon.letts (1/14/2015)
Yes the unit are attached to IT and support the business so they are a little more technical and use SSMS
Yikes!!! On one hand you have people who can't remember the proper date format but on the other hand they are executing procedures directly in SSMS. I would create a simple application they can use instead of directly executing queries in SSMS in production. This process is going to bite you in the rear end someday. I know I am not helping you with your question but your process is so fraught with danger you should fix the process, not the sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2015 at 9:07 am
I would suggest you to follow Sean's advice. You have a simple front end available called Reporting Services (SSRS).
It's a powerful tool that you could use to prevent this kind of errors from the users. You can use the Stairway[/url] in this site to get you started and that could help you a lot for these problems.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply