What the f...? You are right, I tested it.
Look at day number 0: with SQL Server, it is Jan 1, 1900; with Excel: Jan 0, 1900.
The next bug is the leap year: Excel treats 1900 as a leap year (that's wrong!), while SQL Server correctly knows that 1900 is not a leap year.
In sum, those differences account for the 2 days difference in current dates.
I get around this, and other issues such as collation issues between SQL Server and Excel, by always passing dates to Excel as text in a format of dd-MMM-yyyy e.g. '01-Jan-2014'.
It's not pretty but it has worked so far...
“That which can be asserted without evidence, can be dismissed without evidence.”
I am learning to write stored procs on MySQL and am going crazy.
CREATEPROCEDURE CleanCopyEnvData ()
BEGINinsertinto EnvData(UserDate, XAction, Balance, UserID)
CAST(udate as Date)
, CAST(amount1 asDecimal(6,3))
, CAST(amount2 asDecimal(6,3))
, CAST(UID as UnSigned)
I get an error:
Error starting at line : 17 in command -
Error at Command Line : 17 Column : 1
Error report -
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$' at line 1
I am using Oracle SQL Developer and the INSERT statement works ok when executed on its own. I can do these things blindfolded with my arms tied on MSSQL. Any help appreciated.
Just store the filenames without the path to the file server in the database table. Store the path to the file server separately say in another database table and concatenate it with the filename by code whenever user wants to access a file.
Instead of using a plain database back-end, create a server which communicates with the clients and the database. So any client will contact the server which in turn talks to the database and file system, and serves the data/documents back to the clients.