none
Date Conversion Error

    Question

  • I am running the same Delphi 2007 code on two Windows 7 machines using an SQL server database on a remote server. When I run the code one one machine it crashes on the following line: aQuery.FieldByName('curr_date').asDateTime with exception 'EConvert Error: 2013-1-25 00:00:00.000 is not a valid date and time' but on the other machine the code runs without any issues. The 'curr_date' field is a Datetime2 datatype. I found that if I check the value of the 'curr_date' as a string (i.e. aQuery.FieldByName('curr_date').asString), on one machine the date is formatted as '1/25/2013' and on the other machine the date is formatted as '2013-1-25 00:00:00.000'. The machine on which the date is formatted as '2013-1-25 00:00:00.000' is the machine on which the code crashes. The regional settings in Control Panel are identical on both machines. Anyone know what the problem is?

    • Edited by OneCat Friday, October 18, 2013 2:42 PM
    • Changed type OneCat Friday, October 18, 2013 5:13 PM
    • Changed type Kalman TothModerator Friday, October 18, 2013 6:58 PM
    Friday, October 18, 2013 2:24 PM

Answers

All replies

  • Wrong forum. And no, no idea. Especially as the formatting is normally not the problem. Debug it..
    Friday, October 18, 2013 2:33 PM
  • >on one machine the date is formatted as '1/25/2013' and on the other machine the date is formatted as '2013-1-25 00:00:00.000'.

    That appears to be computer setup issue. We cannot really help in this T-SQL forum.

    You can have DATE, DATETIME & DATETIME2 data types in T-SQL and million string date formats:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

     


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, October 18, 2013 3:01 PM
    Moderator
  • Did you know that the only format allowed in ANSI/ISO Standard SQL is 'yyyy-mm-dd HH:MM:ss.sssssss' and you should be using it in every application irregardless of the language?  You also confuse a column and field. This is not an SQL problem; it is a data modeling problem. 

    We also do not do Delphi here; this is the SQL guys. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, October 18, 2013 6:49 PM