1

I need to query a database for some information and then store it into a .csv file in case the dispatch system goes down. I'm running into some issues with the select query as well as the output statement. Here is what I have:

SELECT cmpy, veh, driver, attendant, trainee, unit_code, startdate 
FROM vehicle_schedule 
WHERE startdate >= 2015-07-22
ORDER BY cmpy, veh

The error that I am getting is this:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2010-05-25' to data type int.

I'm not sure what is going on and exactly how to fix the issue. Also I can't get it to output to a file, I get the error: incorrect syntax near 'OUTPUT'

Any help would be amazing!

Drew
  • 24,851
  • 10
  • 43
  • 78
William Hodges
  • 661
  • 10
  • 24
  • put the start date in where condition in '2015-07-22' and cast ('2015-07-22' as varchar) – mohan111 Jul 23 '15 at 02:53
  • You are missing single quotes around the date. – Gordon Linoff Jul 23 '15 at 02:54
  • Why is this upvoted? Also, SQL is a special-purpose programming language mainly used to interact with databases and is not a general purpose, object-oriented one that interacts with external objects and file formats. Hence, you will need some other coding language (Java, Python, PHP, VB, etc.) to output this query to csv. While I say this some dialects like MySQL's `outfile` and PostgreSQL's `Copy` have workarounds. – Parfait Jul 23 '15 at 03:03
  • @Parfait I know that part, I'm using C# to write a script to run every 15 min to query the db in case our dispatching system goes down. So `sql server` does not support an output method? – William Hodges Jul 23 '15 at 03:11
  • 1
    Forgive me but then why not use [C# to save the query to csv](http://stackoverflow.com/questions/1963719/c-sharp-sqlserver-retrieving-results-and-place-in-a-csv-format)? You also avoid having to run a procedure in MSSQL to export the data if possible. As mentioned, abstract your external data processes from internal data handling. Remember a client app like your C# script has a very short lifecycle compared to a database server. – Parfait Jul 23 '15 at 03:24

2 Answers2

1

This is too long for a comment. You are missing single quotes for the date constant:

 WHERE startdate >= '2015-07-22'

The error you are getting is because 2015-07-22 = 1986 (they hyphens are interpreted as minus signs), so the query is turned into:

WHERE startdate >= 1986

Because of the comparison to an integer, SQL Server attempts to convert startdate to an integer . . . and you get a relatively unintelligible error.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your explanation I didn't realize it thought it was 1986. Do you have anything for the output part? – William Hodges Jul 23 '15 at 03:02
  • @WilliamHodges . . . The query in your question does not have an `OUTPUT` clause. In fact, there is no `OUTPUT` clause for `SELECT` queries, so you should just google: "sql server output query to file" and see where that gets you. – Gordon Linoff Jul 23 '15 at 23:58
0

you missed quotes around the date

try this

SELECT cmpy, veh, driver, attendant, trainee, unit_code, startdate 
FROM vehicle_schedule 
WHERE startdate >= '2015-07-22'
ORDER BY cmpy, veh
Ahmed Eid
  • 1,145
  • 8
  • 9