Sql and smalldatetime

Nadir · Post by **Nadir** » Wed May 02, 2007 2:49 am

Hello,

I have some actions that execute sql query like:
insert into table ('%TestId%,'%Date%',...)
The problem is since 2007-12-04 23:59:00 to 2007-01-05 00:01:00
the sql query failed:

Error: Cannot execute SQL query. ErrorCode = 4294967295: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated

And now all works normally, any idea why in this range date SQL return error?

AHM 6.54 running on Win 2003 server
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00

KS-Soft Europe · Post by **KS-Soft Europe** » Wed May 02, 2007 5:32 am

Looks like date format problems. According to the Microsoft, in this case, date format should be: year-month-day (2007-12-04). As I can see from your post, 2007-12-04 should mean the 12th of April, right? But SQL Server considers it as 4th of December. Next value shoud be 2007-13-04. Here is a problem. There is no month number 13 in year. Conclusion: you should adjust date setings.

Regards,
Max

Nadir · Post by **Nadir** » Wed May 02, 2007 6:12 am

As I can see from your post, 2007-12-04 should mean the 12th of April, right?

Right

you should adjust date setings

in misc option I have specified dd/MM/yyyy, Where I must adjust setings? driver ODBC?

KS-Soft Europe · Post by **KS-Soft Europe** » Wed May 02, 2007 6:21 am

Nadir wrote:in misc option I have specified dd/MM/yyyy, Where I must adjust setings? driver ODBC?

dd/MM/yyyy? Actually, I could not see such format in "Date format" combobox in misc option. You have to select "MM/dd/yyyy" or "yyyy-MM-dd". These formats work properly.

Regards,
Max

Nadir · Post by **Nadir** » Wed May 02, 2007 6:57 am

I couldn't use dd/mm/yyyy if I want to use french format?
What is the format use for actions (send email and sql query)? Format from misc option or from regional and language option on server when AHM is running?

KS-Soft Europe · Post by **KS-Soft Europe** » Wed May 02, 2007 7:16 am

Nadir wrote:What is the format use for actions (send email and sql query)? Format from misc option or from regional and language option on server when AHM is running?

Actually, the problem you have faced, is not related to HostMonitor. HostMonitor uses format, specified in "Date Format" box in "Misc" Option. So, when you specify dd/mm/yyyy format, the %Date% variable is resolved as 13/04/2007, in case of the 13th of April 2007. However, your SQL server could accept that format. To figure it out, you may use Convert function in the INSERT statement. So, your query should be like the following:

Code: Select all

insert into table (%TestId%,Convert(datetime,'%Date%',103),...)

Please note: 103 is a British/French date standard:
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Regards,
Max

Nadir · Post by **Nadir** » Wed May 02, 2007 8:22 am

Ok thanks for help