Thank you for the detailed and thorough replies. Much appreciated. I am using the ado/mssql and ado/access drivers and I did see an exception with db.conversion_procs.clear in tinytds.
On Fri, Feb 26, 2021 at 9:48 AM Jeremy Evans <[email protected]> wrote: > On Fri, Feb 26, 2021 at 7:25 AM Michael Davis <[email protected]> > wrote: > >> Your explanation about daylight savings would make since if all the dates >> converted in the same way meaning when I convert them to localtime, I get >> the same date back. One date comes back MST with the right time as >> expected and another comes back CST with right time but they are off by an >> hour off when I convert them to localtime. >> > > I guess we'll just have to agree to disagree about that. From my review of > the data you provided, the timezones returned appear to be either MST > (Mountain Standard Time, -06:00) or MDT (Mountain Daylight Time, -07:00), > depending on the datetime being converted. Again, that's normal Ruby > behavior. > > >> I implemented db.conversion_procs.clear where db is my Sequel database >> connection and here are the results: >> >> - sqlite did exactly what I needed and it helped with another anomaly >> with floats >> - mssql, tinytds, and access did not change and I got the same >> results of a string with the same timezones as before where some dates are >> accurate and others are one hour off when converting to localtime >> >> The conversion_procs method does not exist for tinytds, so you should > have gotten a NoMethodError for db.conversion_procs.clear if using the > tinytds adapter. > > mssql and access aren't Sequel adapters. Maybe you are referring to > ado/mssql and ado/access? The ado adapter wouldn't be affected by it > because of how the timestamps are handled internally by it. Note that even > if I changed things so that the ado adapter did not do any conversion, you > would not get a string back, you would get a WIN32OLE object > (WIN32OLE_TYPE:Field), not a string. > > After some testing, I found that I can modify the ado adapter code in a > way that should provide backwards compatibility by default, but allow > db.conversion_procs.clear to work. I'll probably make that change. > > >> I also implemented cast to string and here is the new sql (SELECT >> EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, CAST(BirthDate AS >> varchar(255)) AS 'BirthDate', CAST(HireDate AS varchar(255)) AS 'HireDate' >> FROM Employees) and it does not work any better, the inserted dates are >> "1952-02-19 14:32:25.342": >> >> - works great for sqlite but not needed since clear conversion_procs >> did the trick >> - for mssql and tinytds I get: "Feb 19 1952 2:32PM" - missing >> seconds and milliseconds, can I convert the CAST() to >> CONVERT(varchar(255), >> BirthDate, 126)? >> - for access (without the milliseconds) I get: "2/19/1952 2:32:25 PM" >> which should be workable however including milliseconds causes an >> exception: OLE error code:80040E07 in Microsoft Access Database Engine >> Data >> type mismatch in criteria expression. >> >> It doesn't look like either option is any better. Any other other >> suggestions I can try? >> > > You probably want to use database-specific timestamp formatting functions > if you don't like the result of casting to strings. You'll have to look at > the documentation for SQL Server and Access to determine which functions > will give you the output you want. > > Thanks, > Jeremy > >> -- > You received this message because you are subscribed to a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/SAOvPRK8GEY/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sequel-talk/CADGZSSenDGd0Jyb%2BSXbSj7LUTdVHKTk%3DiqgSm1o61_AsHekoWg%40mail.gmail.com > <https://groups.google.com/d/msgid/sequel-talk/CADGZSSenDGd0Jyb%2BSXbSj7LUTdVHKTk%3DiqgSm1o61_AsHekoWg%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CALxMgNN8-qxZL9RgiT2ONgONt%2BZQRxQYj%2BW--KjijVH7Q%3Da31g%40mail.gmail.com.
