Conversion DateTime datatype failed Sql Server 200

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Conversion DateTime datatype failed Sql Server 200

Author

Topic

param_joshi
Starting Member

4 Posts

Posted - 2008-11-10 : 02:13:17

Hello All,
I am getting this error "Conversion failed to when converting datetime string to smalldatetime data type" in Sql Server 2008 and my string is 20081110072938 .....I also tried with datetime data type in Sql server but same error.

Exception comes at query

sql.Format("Insert into trade values('%s','%s','%s','%s','%s','%ld')",

m_sTimeStamp,m_sTradeStatus, m_sTradeType, m_sTradeXML, m_sTradeKcc, m_nTrdNumber);

here m_sTimeStamp has the value 20081110072938 .

.. whereas same string is getting converted to datetime format in MySQL ... and working fine with MySQL...

Can anybody help me out of this...

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 02:34:17

try this out

select convert(smalldatetime,STUFF(STUFF(STUFF(STUFF(STUFF('20081110072938',5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'))

param_joshi
Starting Member

4 Posts

Posted - 2008-11-10 : 02:46:54

I cannot use sql functions as I m tring to create sql query in c++.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 03:13:39

then post it in some c++ forums

param_joshi
Starting Member

4 Posts

Posted - 2008-11-10 : 03:36:15

I posted here as my dear friend it is sql exception. as exception comes at
insert into trade('20081110072823','yes','hello');
here first field of SmallDateTime data type.

and same query is properly working when used for MySql...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 04:54:04

i gave you the solution in t-sql. if you want to handle this by c++ code, you need to try your luck in some c++ forums
Also please note that this is MS SQL Server forum not MySQL forum.

param_joshi
Starting Member

4 Posts

Posted - 2008-11-10 : 05:47:59

Yes I could able to insert now by converting string 20081110110223 to 2008/11/10 11:02:23 and then using into insert query.. now both sqlserver as well as MySql could abto insert this into datetime format.. thx vikas for replies.
Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 05:52:15

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources