syntax error : cannot convert datetime

[timos]

Hi to all of you,
I have this problem....
I use a bat file wich calls some .jar files to take data from an sql database to another one. Somewhere inside, there a select query wich results with a syntax error that it cannot convert to datetime. Both the databases are in the same sql server and with the same collation (the one database is a new and empty one). There is a field wich has same dates in the format that we use here in Greece. It is like; dd/mm/yyyy hh:mm pm or am. This field is nvarchar (50) and as obvious it is not the format that the query I run, wants. !
Any ideas ?

Thank all of you in advance

Timos

 

[MeanGreen]

Since the field is varchar, you might try using convert or cast to varchar from your other database where it is datetime or use convert to make the field datetime if it is coming over as varchar. Example:

insert mytable
select convert(varchar(50),<column>,101) from otherdb.dbo.othertable

Hope this helps.

 

[timos]

HI MeanGreen,
thanks for the follow up.
As my knowledge is not very good on the subject, I think that I was not very clear.
This a bat that I run through command prompt. It copy's the data from an existing database, where the specific field id nvarchar (50), to a new database where only the schema exists. So, this bat file, calls some jars that among others, they include a select query which assumes that the specific field is datetime. This is where the problem exists.
The only way that I finally managed to take the data, was to change the date format manually (I was lucky to have only 101 entries.
What you say (If I get it right) is something that I am not able to do, as I cannot modify the query in the specific jar file that includes the query. Also I have tried what you say by running only this query after taking it from the Query Profiler and it was succesfull.

Thank you again for your time
Timos