Conversion DateTime datatype failed Sql Server 2008

[param_joshi]

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...

I am doing all this in VC++.

Can anybody help me out of this...



Thanks

[param_joshi]

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..
Regards

[davide++]

Hi all.

You should use the conversion functions to be sure that your INSERT statement always works fine.
Take a look at Convert function.

[TheCPUWizard]

MUCH more importantly, you should NEVER use string contatenation to build a SQL command. ALWAYS use the provided OBJECT MODEL.

[davide++]

Hi all.

param_josh is developing a VC++ application, where the object model is a set of classes that I found quite uncomfortable to use; it's easier and more flexible to perform the DML operations using some "direct" function or method, that processes the SQL commands as string.
When I develop VC++ applications that use database, usually I develop my own classes that abstract the data-model entities, but the basic operation of writing on db are performed using the CDatabase::ExecuteSQL method (to tell the truth, I use my CDatabase class derived from MFC CDatabase).
So, I agree that it's better using Object Model functionalities but only when they're a valid choice, as Visual Basic shows.
Anyway, there're some situation where you cannot use Object Model easily; for example when you have to insert more than one record, using a SQL statement like this

INSERT INTO MY_TABLE1 (SELECT * FROM MY_TABLE2)

Of course, you can perform the same operation using the Object Model (you need a cursor and then you'll apply the "AddNew" operation inside it), but in this case it's easier using a direct function.

[TheCPUWizard]

Anyway, there're some situation where you cannot use Object Model easily

Strange...In over 15 years using SQL based databases, I have never found such a situation.

Please show a single instance where you can not use a "Command" object along with the associated "Parameter" objects and collections....

Your sample of selecting rows from one source to do an insert into the other can EASILY be accomplished with a single command object, the command itself is a CONSTANT. So a SIMPLE stored proc, and a a command to execute the proc will accomplish the goal, without even using parameters.

[davide++]

Well.

I didn't speak about a particular Object Model, and I said that it's better using Object Model
functionalities when they're efficient. I confess I didn't know Command object, that it's provided
by a particular class of Object Model, and it seems good (I've quickly read something about it), so it's right using it in a vaste range of situations.
But I can imagine some special scenarios, that I've found.
For example:
An INSERT command where the table is variable.
A query that uses dblink that can change (in Oracle environment).
Is possible to manage these situation using parameters (without using a stored procedure)? I don't know. I know that it's very simple build the command as string, and pass it to a direct method.

[Krzemo]

But I can imagine some special scenarios, that I've found.
For example:

All can (and should) be done in stored procedure....

A query that uses dblink that can change (in Oracle environment).

That what aliases are for....

So no concatenating is required!.

I know that it's very simple build the command as string, and pass it to a direct method.

Lazy programmers do it every time, so that's why "hacking" is so simple ...


Best regards,
Krzemo.

[davide++]

Well, if you do all using stored procedures you won't need to know anything about Object ModelS, and basically this is another form of direct call; you don't call the Execute method, but you call another (custom) method, without using the features provided by Object Model.

I don't see how aliases can solve the problem, and permit to avoid concatenation.

What I was trying to tell is that it's important to choose the right tool to solve a specific problem; if a particular Object Model fits my requirements I'll make use of it, else I'll use another tool.
Doesn't matter if the programmer is lazy or hardworking, it's important is if he/she does the proper things, according the specific scenario.
Actually the hardest part in developing is to choose the right way, without being driven by the words NEVER and ALWAYS.

[TheCPUWizard]

Well, if you do all using stored procedures you won't need to know anything about Object ModelS, and basically this is another form of direct call; you don't call the Execute method, but you call another (custom) method, without using the features provided by Object Model.

Agreed about the "right" tool....but disagree that dynamic SQL is EVER the right tool for an application (or even within a production stored procedure.

The risk is just too high. If I had a few hundred dollars for each time I have seen:

; drop table foo; '

be able to "sneak" into dynamic SQL, I probably would be retired.

There are many ways to approach each of the specific items that have been mentioned.

Multiple Rows - consider XML to a Temp Table (believe it or not, it is actually faster in many casee)

Differing Target Tables - consider a "switch" type statement using an integer to invoke the correctly targeted sql (it will also prevent accidently selecting a table which is not intended as a target.

Avoiding Stored Procedures - Really think long and hard about this. It is much easier to put in place rigorous testing and security on stored procedures than it is on applications. In 2006-2007 I personally know of 3 institutions (one financial, two commercial) where developers managed to bury either "bombs" or "thefts" into deployed applications that had their DB permissions set that application code would directly access tables (and one of the cases only allowed READ acccess to the tables!!). Each of the three cost the impacted companies over a million dollars. None of the three now allow ANYTHING but SP access (using throughly vetted impleentations and requiring multiple individuals to put a new SP into production)