Datetime string

Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 =A4W=A4=C8 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 =A4W=A4=C8 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 =A4W=A4=C8 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 =A4W=A4=C8 09:24:17 .
Thanks.http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||Hi
declare @.dt varchar(20)
set @.dt='2006/4/26 10:22:55'
create table #table (c datetime)
insert into #table (c)
select cast(rtrim(y*10000+m*100+d)+' '+ t as datetime)
from
(
select year(@.dt) as y,month(@.dt) as m ,day(@.dt)as d,
right(@.dt, CHARINDEX(' ', REVERSE(@.dt))-1) t
) as der
select * from #table
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||hon123456 (peterhon321@.yahoo.com.hk) writes:
> I got a fieldA which is datetime , when I check the value
> in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
> When I get this field to a ADODB.recordset named rs_A, rs_A
> returns :
> 2006/4/26 W 09:24:17 ,which is date time string format
> with Chinese String.
> When I try to insert '2006/4/26 W 09:24:17' into a datetime
> field, the query analyzer complains
> Syntax error converting datetime from character string.
> When I cast('2006/4/26 W 09:24:17' as datetime), the query
> analyzer also complains
> Syntax error converting datetime from character string.
> My quetions is how to keep the data time value as 2003-10-27
> 10:55:00.000 but not 2006/4/26 W 09:24:17 .
One answer to that particular question, is to change your regional settings
to Swedish, or at least change the datetime format in regional settings. I
would not recommend that though.
What I don't really understand is you need to take a value from an
ADO recordset and paste into Query Analyzer.
When you pass dates to and from SQL Server, you should do so in binary
format. The client API will then convert from/to string format according
to regional settings.
I suspect that you do something like this in your ADO code:
sql = "SELECT ... FROM tbl WHERE datetimecol = '" & rs("dt") & "'"
Don't do that. Run a parameterised query instead. Here is a quick sample
query:
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If
If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If
Set rs = cmd.Execute
This example does not includ a datetime parameter, but at least you get
to see the principle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx