convert

hi,
How do I convert this into smalldatetime please?
I am doing this because there is a field of type varchar which has to go into a separate table with field of type smalldatetime.

select convert(smalldatetime, '14/10/04', 101)

This is what I have but the error is:
Conversion failed when converting character string to smalldatetime data type.

This looks like a locale issue; try executing a SET DATEFORMAT DMY before executing your convert. You might also want to give a look to Umachandar's comments in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597495&SiteID=1

Note the suggestion to try to use one of the ISO formats when possible.

Code Snippet

set dateformat dmy

select convert(varchar, cast('14/10/7' as datetime), 101) [a date/time]

/*
a date/time
10/14/2007
*/

select cast('14/10/7' as smalldatetime) as [converted]

/*
converted
2007-10-14 00:00:00
*/

|||

Try this:

select convert(smalldatetime, '10/14/2004', 101)

You already had smalldatetime and the system was probably expecting the MM/DD/YYYY format rather than DD/MM/YYYY

sqlsql