cast problem, I think 
Author Message
 cast problem, I think

Hi:

I'm having trouble casting...I think?  

I get the browser error...Syntax error converting datetime from character string.

Below is the parameter from my ASP.NET code...the parameter comes from a dropdownlist control named Date and the value being used is entered as 12/20/2003, and is evidently being read as a string.  Below the parameter code is the sproc.

How, exactly, do I convert the string in the dropdownlist to a date so that it will still work in my sproc...if that is my problem?

Thanks,

Paul







return
GO



Sat, 10 Jun 2006 04:31:05 GMT
 cast problem, I think

Paul,

  The error is not where you expect it, I think.  Because you have

evaluate an expression of the form <string> + <string> + <string> +
<datetime> + <string>.  In SQL Server, when the + operator appears
between expressions of different types, the lower-precedence type is the
T-SQL type hierarchy is converted before + is applied.  Since <nvarchar>
is lower than <datetime>, what happens at the third + is



where + is datetime addition, not string concatenation.

While I think the fact that you are passing a table name as a parameter
is in itself not a good sign, if you have no choice but to create your
query dynamically, I'd suggest the following change:

Be explicit about date<-> string conversions.  If you know you have
passed the correct datetime value, then convert it to a language and
format-independent string form to build the query:


SK

Quote:

>Hi:

>I'm having trouble casting...I think?  

>I get the browser error...Syntax error converting datetime from character string.

>Below is the parameter from my ASP.NET code...the parameter comes from a dropdownlist control named Date and the value being used is entered as 12/20/2003, and is evidently being read as a string.  Below the parameter code is the sproc.

>How, exactly, do I convert the string in the dropdownlist to a date so that it will still work in my sproc...if that is my problem?

>Thanks,

>Paul







>return
>GO



Sat, 10 Jun 2006 05:08:16 GMT
 cast problem, I think


Also, Rather than
MM/DD/YYYY

I would consider passing the date in a non region specific format:
YYYYMMDD

SQL should always convert the string to the correct datetime value.

Regards,
Damien.

Hi:

I'm having trouble casting...I think?  

I get the browser error...Syntax error converting datetime from character string.

Below is the parameter from my ASP.NET code...the parameter comes from a dropdownlist control named Date and the value being used is entered as 12/20/2003, and is evidently being read as a string.  Below the parameter code is the sproc.

How, exactly, do I convert the string in the dropdownlist to a date so that it will still work in my sproc...if that is my problem?

Thanks,

Paul







return
GO



Sat, 10 Jun 2006 05:09:07 GMT
 cast problem, I think
Hi Steve,

You probably meant style 112?  The following:

select convert(char(8), current_timestamp, 126)

yields:

2003-12-



Sat, 10 Jun 2006 05:56:37 GMT
 cast problem, I think
I meant style 126.  While style 112 is "SQL Server-safe" (interpreted
the same way regardless of language and dateformat settings), style 126
is better on a couple of counts.  For dynamic SQL, it is safer, since it
won't make sense as an integer if the quotes are accidentally left out,
and it is a more universally accepted format outside SQL Server, since
it is an ISO standard format.

The T between the date and time in this format prevent SQL Server from
interpreting it according to the dateformat setting in place, unlike
every other delimited date format.

I haven't just now rechecked that this is the case, but I'm pretty sure
- let me know if you think I'm wrong.

SK

Quote:

>Hi Steve,


>You probably meant style 112?  The following:

>select convert(char(8), current_timestamp, 126)

>yields:

>2003-12-



Sat, 10 Jun 2006 18:50:05 GMT
 cast problem, I think
That sounds fine, but if you meant 126, then you didn't mean CHAR(8)...
because, as I mentioned, this returns "YYYY-MM-" ... not a valid dateby
*any* standard I'm aware of.  :-)


Quote:
> I meant style 126.  While style 112 is "SQL Server-safe" (interpreted
> the same way regardless of language and dateformat settings), style 126
> is better on a couple of counts.  For dynamic SQL, it is safer, since it
> won't make sense as an integer if the quotes are accidentally left out,
> and it is a more universally accepted format outside SQL Server, since
> it is an ISO standard format.

> The T between the date and time in this format prevent SQL Server from
> interpreting it according to the dateformat setting in place, unlike
> every other delimited date format.

> I haven't just now rechecked that this is the case, but I'm pretty sure
> - let me know if you think I'm wrong.



Sat, 10 Jun 2006 18:56:22 GMT
 cast problem, I think
Ahhh.  You are right - my mistake, it should be char(23).  Thanks!!

SK

Quote:

>That sounds fine, but if you meant 126, then you didn't mean CHAR(8)...
>because, as I mentioned, this returns "YYYY-MM-" ... not a valid dateby
>*any* standard I'm aware of.  :-)



>>I meant style 126.  While style 112 is "SQL Server-safe" (interpreted
>>the same way regardless of language and dateformat settings), style 126
>>is better on a couple of counts.  For dynamic SQL, it is safer, since it
>>won't make sense as an integer if the quotes are accidentally left out,
>>and it is a more universally accepted format outside SQL Server, since
>>it is an ISO standard format.

>>The T between the date and time in this format prevent SQL Server from
>>interpreting it according to the dateformat setting in place, unlike
>>every other delimited date format.

>>I haven't just now rechecked that this is the case, but I'm pretty sure
>>- let me know if you think I'm wrong.



Sat, 10 Jun 2006 18:58:46 GMT
 cast problem, I think
Steve:

I find your explanations to be the easiest to follow that I've encountered.

Between you, Foo Man Chew and Damian, the solution worked as you all suggested.

The fact that you folks take the time to help less knowledgable people like me is a blessing.

Thanks and Have a good holiday season.

Paul



Sat, 10 Jun 2006 19:11:14 GMT
 cast problem, I think

Quote:
> Ahhh.  You are right - my mistake, it should be char(23).  Thanks!!

No problem.  I don't have any issue with 126 vs. 112, I just knew that
CHAR(8) would work for 112 but not 126.  So, either the style was indicated
incorrectly, or the size of the CHAR.  :-)

Happy holidays.



Sat, 10 Jun 2006 19:21:14 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. CREATE CAST requires immutable cast function?

2. check validity of cast before performing cast?

3. CREATE CAST requires immutable cast function?

4. Thinking about WinHelp Tools? Think $$$

5. Thinking about WinHelp Tools? Think $$$

6. OpenRoad: Problem with method invocation and casting

7. cast problem -- int to varchar

8. casting problem

9. problems with CAST :-( under postgres ...

10. P4W: Casting Problem when doing calculation

11. problem running an update with a cast

12. SQL problem:cast from VARCHAR to TEXT


 
Powered by phpBB® Forum Software