SQL Server error: Conversion failed when converting datetime from character string.

[fishjie]

I'm working on a stored procedure in Microsoft SQL Server that currently gives the error:
Conversion failed when converting datetime from character string.

Here is a code snippet, but let me first explain what is going on. The @Query parameter is going to be run later on as part of a call to sp_send_dbmail.

SET @Query = 'SELECT * FROM dbo.Test WITH (NOLOCK) WHERE Test.TestID = 1 AND Test.Date > ''' + CAST(@Today AS nvarchar(20)) + ''''

There are multiple queries but the first thing I wanted to rule out is that casting nvarchar(20) should be sufficient. Its possible that when it cast the date as a string, when it tried to execute the query later it wasn't enough to convert to a date time. Is casting from a datetime to a length 20 nvarchar enough?

 

[KB]

I am certain there has to be a better way than this. Building SQL inside a SProc eliminates the benefits of SPs: compiled and stored execution plans.

Execute your SP in Query Analyzer and do a PRINT @Today statement to see what is in @Today variable

nvarchar(20) might not be enough to handle the complete date in the default format: 2007-08-01 08:53:02.080
upping it is easy nvarchar(25)



You can also try the CONVERT function instead of cast

CONVERT(nvarchar(20), @Today, 101)

see http://sqljunkies.com/HowTo/66...9578-9A1C7FD826E5.scuk

 

[brandonb]

(edit: KB beat me to it!)

Hi fishjie,

I don't believe SQL likes the CAST on datetimes very much. Because it doesn't know which format of a date you want to use. Be it "mm/dd/yyyy" or "January 1st, 2007", etc. I believe you will want to use the CONVERT command with a datetime stamp. It almost works identical to CAST except it has an extra parameter for converting datetime stamps.

SET @Query = 'SELECT * FROM dbo.Test WITH (NOLOCK) WHERE Test.TestID = 1 AND Test.Date > ''' + CONVERT(@Today, nvarchar(20), 121) + ''''

Something like that. I dont have SQL installed on this computer to look at the reference, but It hink its similar to that, and the last parameter is how to format the string, I vaguely remember using 121 all the time. Give it a shot and see if it works better.

Also, is @Query setup as NVARCHAR? If its only setup as a VARCHAR you probably can't cast it with nvarchar as that is unicode and varchar is not.

 

[fishjie]

yeah, query is an nvarchar.

ok I will try convert. on my dev machine, casting a datetime to nvarchar yields this format:
"Aug 1 2007 6:32PM"

I will use convert to explicitly cast it to this format:
mon dd yyyy hh:miAM

That is 19 characters long. So should I use nvarchar(19) or nvarchar(20)? Do you need to include the null terminator in SQL?

 

[KB]

No null terminators are needed. Why are you so concerned about nvarchar sizing? It won't affect performance unless you go crazy. Just use an nvarchar(30) and be done with it.

 

[fishjie]

well if the nvarchar isnt long enough, it will definitely cause the conversion failed error (when it tries to conver the string back to a datetime). And I was hoping someone could give me the exact length. It might come in handy in the future you never know.

 

[KAMAZON]

I don't know what's more sad. The fact that I clicked on your link 'pix of my sexy computer', clicked on the RAM memory and went OOoooh; OR the fact that it already had 6 other views before I hit it.

 

[napster52]

For relational data, T-SQL has been augmented with error handling features and support for recursive queries. SQL Server 2005 has also been enhanced with new indexing algorithms and better error recovery systems. Data pages are checksummed for better error resiliency, and optimistic concurrency support has been added for better performance.

========================================================

napster

I didn't understand anything about women until i started listening to this guy...his dvd distills all his secrets into one great video

http://www.TheSeductionKing.com/DVD]http://www.TheSeductionKing.com/DVD[/url]