June 23, 2016 at 10:09 am
dhaveedh (6/23/2016)
In your last post you said SELECT CONVERT(Datetime, '29-Feb-2') returns an error because SQL Server does not accept single digit years?
Why does SELECT CONVERT(Datetime, '28-Feb-2') return 2002-02-28 00.00.00.000 without any failure? Maybe I missed something from your previous explanation.
Is 2002-02-28 a valid date? The point is, if you use the correct error checking functions, it will tell you when there is a error and give you the opportunity to handle it. Just like any other programming language.
June 23, 2016 at 10:17 am
trboyden (6/23/2016)
dhaveedh (6/23/2016)
In your last post you said SELECT CONVERT(Datetime, '29-Feb-2') returns an error because SQL Server does not accept single digit years?
Why does SELECT CONVERT(Datetime, '28-Feb-2') return 2002-02-28 00.00.00.000 without any failure? Maybe I missed something from your previous explanation.
Is 2002-02-28 a valid date? The point is, if you use the correct error checking functions, it will tell you when there is a error and give you the opportunity to handle it. Just like any other programming language.
It is a valid date.
What do you mean by "the correct error checking functions"?
June 23, 2016 at 10:47 am
Luis Cazares (6/23/2016)
It is a valid date.What do you mean by "the correct error checking functions"?
http://www.w3schools.com/sql/sql_dates.asp
http://www.w3schools.com/sql/func_convert.asp
https://msdn.microsoft.com/en-us/library/ms187347.aspx ( ISDATE() Function)
https://technet.microsoft.com/en-us/library/ms179465(v=sql.105).aspx ( Handling Database Engine Errors )
http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (See datetime best practices section)
But of course, Google is your friend here...
June 23, 2016 at 11:00 am
trboyden (6/23/2016)
Luis Cazares (6/23/2016)
It is a valid date.What do you mean by "the correct error checking functions"?
http://www.w3schools.com/sql/sql_dates.asp
http://www.w3schools.com/sql/func_convert.asp
https://msdn.microsoft.com/en-us/library/ms187347.aspx ( ISDATE() Function)
https://technet.microsoft.com/en-us/library/ms179465(v=sql.105).aspx ( Handling Database Engine Errors )
http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (See datetime best practices section)
But of course, Google is your friend here...
You don't have to send me links, I know how to handle dates. I'm asking you to explain your assertion which can be misinterpreted by newbies. You should know that the internet is full of bad "best practices" such as the one you posted before.
June 23, 2016 at 11:21 am
trboyden (6/23/2016)
Luis Cazares (6/23/2016)
It is a valid date.What do you mean by "the correct error checking functions"?
http://www.w3schools.com/sql/sql_dates.asp
http://www.w3schools.com/sql/func_convert.asp
https://msdn.microsoft.com/en-us/library/ms187347.aspx ( ISDATE() Function)
https://technet.microsoft.com/en-us/library/ms179465(v=sql.105).aspx ( Handling Database Engine Errors )
http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (See datetime best practices section)
But of course, Google is your friend here...
None of your links present any information to support your assertion that 2002-02-28 00.00.00.000 is not a valid date. The ISDATE function even confirms it is a date.
June 23, 2016 at 12:26 pm
dhaveedh (6/23/2016)
None of your links present any information to support your assertion that 2002-02-28 00.00.00.000 is not a valid date. The ISDATE function even confirms it is a date.
So connect the dots... How did you determine whether the date was valid or not? You used a function that checked it before you used the data right? Is that not what ISDATE() did, so is that not a data checking function? Does CONVERT() not do the same thing? Is it not a standard programming practice to check data, especially user provided data, before you use it? This is a wide ranging topic that covers proper data type conversions to SQL injection issues. I am really not trying to be a Dbag, I am trying to get you to think for yourself. This really is programming logic 101 level stuff.
1) User provides a value
2) Decision: Do I trust the value or do I need to check it?
3A) Action if I choose to check it: CONVERT in a TRY/CATCH or ISDATE() in an IIF
3B) Action if I choose to trust it: ...
4A) Action if date is bad: ...
4B) Action if date is good: ...
Etc...
June 23, 2016 at 1:44 pm
trboyden (6/23/2016)
dhaveedh (6/23/2016)
None of your links present any information to support your assertion that 2002-02-28 00.00.00.000 is not a valid date. The ISDATE function even confirms it is a date.So connect the dots... How did you determine whether the date was valid or not? You used a function that checked it before you used the data right? Is that not what ISDATE() did, so is that not a data checking function? Does CONVERT() not do the same thing? Is it not a standard programming practice to check data, especially user provided data, before you use it? This is a wide ranging topic that covers proper data type conversions to SQL injection issues. I am really not trying to be a Dbag, I am trying to get you to think for yourself. This really is programming logic 101 level stuff.
1) User provides a value
2) Decision: Do I trust the value or do I need to check it?
3A) Action if I choose to check it: CONVERT in a TRY/CATCH or ISDATE() in an IIF
3B) Action if I choose to trust it: ...
4A) Action if date is bad: ...
4B) Action if date is good: ...
Etc...
Why would you allow the user to provide an invalid value in the first place?
We have data types for a reason, using strings to handle values is lazy programming and the cause for 1+1=11.
June 23, 2016 at 1:52 pm
Luis Cazares (6/23/2016)
Why would you allow the user to provide an invalid value in the first place?We have data types for a reason, using strings to handle values is lazy programming and the cause for 1+1=11.
You'd have to ask the O/P on that one, I didn't write his code.
June 23, 2016 at 1:56 pm
Luis Cazares (6/23/2016)
trboyden (6/23/2016)
dhaveedh (6/23/2016)
None of your links present any information to support your assertion that 2002-02-28 00.00.00.000 is not a valid date. The ISDATE function even confirms it is a date.So connect the dots... How did you determine whether the date was valid or not? You used a function that checked it before you used the data right? Is that not what ISDATE() did, so is that not a data checking function? Does CONVERT() not do the same thing? Is it not a standard programming practice to check data, especially user provided data, before you use it? This is a wide ranging topic that covers proper data type conversions to SQL injection issues. I am really not trying to be a Dbag, I am trying to get you to think for yourself. This really is programming logic 101 level stuff.
1) User provides a value
2) Decision: Do I trust the value or do I need to check it?
3A) Action if I choose to check it: CONVERT in a TRY/CATCH or ISDATE() in an IIF
3B) Action if I choose to trust it: ...
4A) Action if date is bad: ...
4B) Action if date is good: ...
Etc...
Why would you allow the user to provide an invalid value in the first place?
We have data types for a reason, using strings to handle values is lazy programming and the cause for 1+1=11.
EXACTLY; Correct the DDL, use a properly-typed and parameterized query, kick the errors back to the Application level.
Using proper Data Types at the application level will throw an exception when invalid data is presented, and the application should catch it and display an appropriate message via the UI
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply