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

Datetime returns NULL from stored proc in VB

I execute a stored procedure in VB and get several results... the last one (a date time field) is always returned as NULL even though I can see in enterprise manager that it IS infact set... any ideas?How are you retrieving the results? In a recordset or as a command ouput parameter?|||I'm using ADODB.Recordset|||Huh. I think we're gonna need to see some code on this one.|||I am using a record set. Hopefully you are using ADO 2.7 or higher by now...|||I meant, it would be helpful to see the code you are using, to help troubleshoot the problem.

But, try inserting this after you retrieve your recordset:

Dim mField As Field
For Each mField In mRecordset.Fields
Debug.Print mField.Name & ": " & mField.Type
Next mField

(replacing mRecordset with your recordset name)

Just to see what datatype is being returned. This may help point to the problem.|||Thanks, I really appreciate it... I'll try it right now :)|||The value of the field type is 135 which is

Const adDBTimeStamp = 135 (&H87)|||Any one else got an idea?|||Is TimeStamp the field type in SQL, or is it supposed to be DateTime? They are not the same.|||It's a DateTime data type (just checked in EM)... I know they're not the same... I don't know why it's returning time stamp and why it's coming up NULL (I'm retrieving it into a string, then I tried a Date type)|||As a test, in your sp, try casting the column to be DATETIME in the output query. See if this changes the datatype in VB.|||I fixed it :-D

DATETIME field

I have a CreatedDate column of type datetime. I am trying to query for
CreatedDate = somedate, but not results are returned. I understand this is
because the time is also included in the data will never equal a specific
date. BOL recommends doing a string comparison using LIKE; however, it
doesn't return results either. Any suggestions?
WBWB,
What is the datatype somedate? Try CONVERTing CreatedDate to a format that
doesn't include the time:
SELECT CreatedDate FROM YourTable
WHERE CONVERT(char(8), CreatedDate, 112) = somedate
-Andy
"WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
>I have a CreatedDate column of type datetime. I am trying to query for
> CreatedDate = somedate, but not results are returned. I understand this
> is
> because the time is also included in the data will never equal a specific
> date. BOL recommends doing a string comparison using LIKE; however, it
> doesn't return results either. Any suggestions?
> WB
>|||somedate is usually an entry like '04/12/2005'
"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:%23dR1D84PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> WB,
> What is the datatype somedate? Try CONVERTing CreatedDate to a format
that
> doesn't include the time:
> SELECT CreatedDate FROM YourTable
> WHERE CONVERT(char(8), CreatedDate, 112) = somedate
> -Andy
> "WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
specific
>|||> somedate is usually an entry like '04/12/2005'
But is that April 12th or December 4th? You can Google this group and find
tons of dicussions on this topic.
Good luck.
-Andy|||Try type 101 instead of 112, although it should work either way.
"WB" <none> wrote in message news:eUren%234PFHA.2788@.TK2MSFTNGP09.phx.gbl...
> somedate is usually an entry like '04/12/2005'
> "Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
> news:%23dR1D84PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> that
> specific
>|||> BOL recommends doing a string comparison using LIKE;
BOL is WRONG, in my opinion.
If you want to find values for today, use a range query:
DECLARE @.dt SMALLDATETIME
SET @.dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT columns FROM table
WHERE dateColumn >= @.dt
AND dateColumn < (@.dt + 1)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
however, it
> doesn't return results either. Any suggestions?
> WB
>|||I suggest you check out http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
>I have a CreatedDate column of type datetime. I am trying to query for
> CreatedDate = somedate, but not results are returned. I understand this i
s
> because the time is also included in the data will never equal a specific
> date. BOL recommends doing a string comparison using LIKE; however, it
> doesn't return results either. Any suggestions?
> WB
>