I find it easier to provide a solution when people provide a clear explanation on what they are trying to do, instead of a bunch of code that doesn't work.

You didn't really explain...

I don't think that you can come up with a simple rule.  You need to decide what to do in every situation.

If you actually do not know a value or...

You shouldn't be too surprised.

All you did was post a bunch of code with a bunch of errors in it, without a clear explanation of what you are trying to...

That code would produce an error if the dateformat in non-US, for example dmy is normal in UK.

I think you have a typo.  This will do what you want.

select dateadd(dd,datediff(dd,0,getdate()),0)

More than likely, you are getting this problem because you have a computed column or indexed view on a table.  There are specific options that have to be set correctly with...

What if you didn't know anything about Muppets?  I guess you wouldn't get the job.

If someone asked me that question, I would take it as a sign that they were...

select getdate(), convert(varchar(10),getdate(),121)

This should do it.

select
 ReadOnly = databaseproperty(a.name ,'IsReadOnly'),
 a.name
from
 master.dbo.sysdatabases a
order by
 a.name

You could have them insert their query results in declared tables ( declare @t table (...col defs...) ) first  before returning any data to the client so there are no issues with locks.

This seems simpler and looks like it works OK.

select
 [DateTime] =
 convert(datetime,substring(MyDate,1,8))+
 -- Add hours
 dateadd(hh,0+substring(MyDate,9,2),0)+ 
 -- Add minutes
 dateadd(mi,0+substring(MyDate,11,2),0)+
 -- Add seconds
 dateadd(ss,0+substring(MyDate,13,2),0)+
 -- Add millseconds, and right fill with zeros
 dateadd(ms,0+left(substring(MyDate,15,3)+'000',3),0)
from
 (-- Test Data
 Select Mydate = '20071221234455' union all
 Select Mydate...

SQL Server generates the account and password, and you cannot set either.

The account is a local account on the SQL Server, and so is limited to access to the local server...

Just type in the URL of the network directory.

Take a look at the info on this link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

select
 Hours = datediff(ms,T1,T2)/3600000.000
from
 (selectT1 = convert(datetime,'15:01:00'),
  T2 = convert(datetime,'18:00:00') ) a

Result:

Hours                      
-------------------------- 
2.98333333333

(1 row(s) affected)