Convert nvarchar to datetime

[rkrause]

I have a field in a sql table called prodate but the field is a nvarchavr(max). data in this field comes in and looks like this 10/12/2011 or 2/15/2012, ect.

how can i convert that to a datefield?

i have tried cast(proddate, as datetime)
i have tried using convert and nothing seems to work for me, any help would be great.

 

[pbaldy]

The proper syntax would be:

CAST(proddate as datetime)

You'd use CONVERT if you wanted a particular format.

 

[rkrause]

this is what i get for an error....
below is my query

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.


select vatnumber, tablenumber,galsofwater, proddate, HCVRecipe_name, watertemp,
Cast(proddate as datetime)
FROM VQ_PROD_TABLE_WATER

 

[pbaldy]

Is there a space after "as"? There needs to be. I don't have a field with that data type, but I tested this:

CAST('12/31/2012' as datetime) AS DateFormat

and it worked as expected. I also tested with a variable that was declared as varchar and it worked as well. You're sure of the format of the string? Could there be a record with an nonconvertible string in it?

 

[rkrause]

yes i have a space after as. just didnt seem to copy over.

i had 3 records where proddate was null, i put some data in there thinking that was it, but im still getting that error below.
here is my field infor - PRODDATE (nvarchar(max), null)


Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

any other ideas?

 

[pbaldy]

It smells like it's data related. I don't suppose you can push the table (or at least the proddate field) out to Access or Excel and post it here so I can push it up to SQL Server and test it?

 

[rkrause]

here it is in excel

 

[pbaldy]

Sort that by proddate ascending and starting at row 5963 you'll see values like:

11/7/20111

which is not a valid date.

 

[rkrause]

I removed the 32 records with the11/7/20111 but im still getting the same error

 

[rkrause]

i didnt realize there were other dates like that in the table. works now. thanks.

 

[pbaldy]

There were hundreds as I recall. Glad we got it sorted out.