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

content of REPLDATA directory

Hi!
There are collecting snapshot agents (SQL7.0) generated files in the separate directories (named like 20030923020007, 20030716020008, and so on) in my ReplData directory.
Why does count of such directories increase without interruption?
Can I delete directories with old date of modifying from my C:\MSSQL7\REPLDATA\unc\ directory?When a snapshot is recreated, SQL will make a new subfolder with datetime stamp as its name to put the data in Repldata folder. There should be a clean up job to delete the subfolder once the snapshot is delivered. My guess is the process has been interrupted so the cleanup step was not completed. You can delete the subfolder since it's no longer being used.