Update SQL Database help

[tdignan87]

Hi
I have a datetime stored as double table format in a firebird database. I need to update the DB for records older than 5 days old. Can anyone help me with how to convert the table to datetime, and then convert back? Or any best practice?
Please see example of my code below. Thanks

  String deletecommod = db.Query<String>("UPDATE REQUIREMENTSH SET REQUIREMENTSH.DELETED " + DateTime.Now.ToOADate() + " WHERE  REQUIREMENTSH.CREATIONDATE >= -5, GETDATE()") .FirstOrDefault();

 

[Skydiver]

Since your database contains double, you should be using ToOADate() to get a double that you can compare against values in the database.

 

[NoUserHere]

And once you do that, you can then do this to convert the date to our modern way with something like :

        private string Convert_FromOADate(string dateFormat)
        {
            bool parsed = double.TryParse(dateFormat, out double parsed_double);
            if (parsed)
            {
                DateTime ConvertToDate = DateTime.FromOADate(parsed_double);
                return ConvertToDate.ToShortDateString();
            }
            return string.Empty;
        }

You would call it like this :

            string Conversion = Convert_FromOADate("38456");
            if (Conversion != string.Empty)
                Console.WriteLine(Conversion);

If you don't know what the code does, ask me and I will explain it. 38456 would obviously be the date you would input here from your database. 38456 Gives you a date in and around 2005

Woops, fixed typo

 

[NoUserHere]

Also :

I mean its working for a DateTime.FromOADate(657435.0);

Then you would do the reverse of what I've given you already, but I don't see a point in why you would do that, unless you are unsure of the values of dates in doubles you need to query against? As per your request :

Can i do anything to convert REQUIREMENTSH.DOUBLE to DateTime in order to update the table for records older than 5 days?

On that same post you said : The issue is skydiver i cant convert it to a double - Which format is your dates in the database field(s)? Lets start clarifying what your situation is once again...

Here's what I think. You've learned to convert from OA and now you don't know how to query your database with your non (double) OA values. Since your database table holds double values; but If you look at what I done above, you can get some ideas to reverse the query lookup by converting a modern date to OA and then to double. You should then be able to query your database with the date format you constructed using modern practices once you convert it back to a double value. Post back what you've tried.

 

[tdignan87]

Hi
I will start again. Apologies
Basically we have this firebird DB which stores the datetime as a DOUBLE. I need to update the STOCKDET quantity column to 0 for records in the STOCKHDR.RECEIVED for dates older than 30 days.

Example of code below

String deletecommod = db.Query<String>(" UPDATE STOCKDET SET STOCKDET.QTY = 0 WHERE CAST (STOCKHDR.RECEIVED AS DATETIME)  >= -30, GETDATE()").FirstOrDefault();
                Console.WriteLine("Successful");

Of course this will not work as I need to convert the STOCKHDR.RECEIVED to DATE format.

Where I am getting stuck and confused is
1. How do I convert DateTime.FromOADate(657435.0); for the STOCKHDR.RECEIVED column


Sorry i know its a pain dealing with newbies but i am in process of learning more.
Thanks

 

[NoUserHere]

we have this firebird DB which stores the datetime as a DOUBLE

So when you query your database, you will be receiving double values. Which is no problem.

Of course this will not work as I need to convert the STOCKHDR.RECEIVED to DATE format.

There must be mist or fog in-front of your eyes. Why do you think I gave you this? :

private string Convert_FromOADate(string dateFormat)
{
bool parsed = double.TryParse(dateFormat, out double parsed_double);
if (parsed)
{
DateTime ConvertToDate = DateTime.FromOADate(parsed_double);
return ConvertToDate.ToShortDateString();
}
return string.Empty;
}

You would call it like this :

string Conversion = Convert_FromOADate("38456");
if (Conversion != string.Empty)
Console.WriteLine(Conversion);

If you don't know what the code does, ask me and I will explain it. 38456 would obviously be the date you would input here from your database. 38456 Gives you a date in and around 2005

???

Where I am getting stuck and confused is
1. How do I convert DateTime.FromOADate(657435.0); for the STOCKHDR.RECEIVED column

That's what I gave you the code for. Upon querying your database, you then need to construct your datetime object using modern practices, then and while its in datetime format, you need to do the reverse of the code I gave you. And convert your dates back to a double. It's very straight forward, and I've already done half of it for you.

 

[tdignan87]

2005 is showing in console but the DB update isnt working.
Its straightforward to you, but not to me i am learning. Here is a total example of my code

 try
            {

                 string Convert_FromOADate(string dateFormat)
                {
                    bool parsed = double.TryParse(dateFormat, out double parsed_double);
                    if (parsed)
                    {
                        DateTime ConvertToDate = DateTime.FromOADate(parsed_double);
                        return ConvertToDate.ToShortDateString();
                    }
                    return string.Empty;
                }

                string Conversion = Convert_FromOADate("38456");
                if (Conversion != string.Empty)
                 Console.WriteLine(Conversion);


                FbConnectionStringBuilder rfs = new FbConnectionStringBuilder();
                rfs.DataSource = "localhost";
                rfs.Port = 3050;
                rfs.Database = @"192.168.7.57:C:\databases2\TEST2.IDB";
                rfs.UserID = "sysdba";
                rfs.Password = "masterkey";
                rfs.ServerType = FbServerType.Default;

                FbConnection db = new FbConnection(rfs.ToString());
                db.Open();

            


                String deletecommod = db.Query<String>(" UPDATE STOCKDET SET STOCKDET.QTY = 0 WHERE CAST (STOCKHDR.RECEIVED AS DATETIME)  >= -30, GETDATE()").FirstOrDefault();
                Console.WriteLine("Successful");

                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
                mail.From = new MailAddress("alerts@paterson-arran.com");
                mail.To.Add("tdignan@paterson-arran.com");
                mail.Subject = "RFS Schedule Clean successful";
                mail.Body = "RFS Stock Cleaned <br> Stock older than 30 days has successfully been written off.</br>";

                SmtpServer.Port = 25;
                SmtpServer.Credentials = new System.Net.NetworkCredential("alerts@paterson-arran.com", "");
                SmtpServer.EnableSsl = true;
                mail.IsBodyHtml = true;

                SmtpServer.Send(mail);


      
              

            }
            catch (Exception ex)
            {
                Console.WriteLine("Not successful");
                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");

                mail.From = new MailAddress("alerts@paterson-arran.com");
                mail.To.Add("tdignan@paterson-arran.com");
                mail.Subject = "RFS Stock Det Clean failed to update stock >30 days to 0";
                mail.Body = "Exception Details" + ex.ToString();

                SmtpServer.Port = 25;
                SmtpServer.Credentials = new System.Net.NetworkCredential("alerts@paterson-arran.com", "");
                SmtpServer.EnableSsl = true;
                mail.IsBodyHtml = true;

                SmtpServer.Send(mail);
            }

      


        }
    }
}

 

[NoUserHere]

Dear oh dear.... Pick up a book on C# basics today.

This :

38456

Should be replaced with a date you received from your database. And this does not belong inside a method. It is a function and is designed to be used as one. Paste this BELOW one of your other methods.

        private string Convert_FromOADate(string dateFormat)
        {
            bool parsed = double.TryParse(dateFormat, out double parsed_double);
            if (parsed)
            {
                DateTime ConvertToDate = DateTime.FromOADate(parsed_double);
                return ConvertToDate.ToShortDateString();
            }
            return string.Empty;
        }

As par my instructions, this code would go inside a method which receives your date variables :

            string Conversion = Convert_FromOADate("38456");
            if (Conversion != string.Empty)
                Console.WriteLine(Conversion);

And to convert a double value from your database date table, you simply get the receiving double value variable and insert it here string Conversion = Convert_FromOADate("38456");. Conversion will then hold your date in normal date format "year/month/day".

To query against your database, you simply write the reverse of the code I gave you to convert a string formatted date to double. Something to the effect of this :

            DateTime dateTime = new DateTime(2019, 6, 4);
            double date = Convert.ToSingle(dateTime.ToOADate());
            Console.WriteLine(date.ToString());

 

[tdignan87]

I have picked up a book on C# basics.
Clearly i am lacking the knowledge. I will give this another try once i have finished the book.
Thanks for taking time to help me.

 

[Skydiver]

This isn't a matter of learning C#. It's a matter of understanding logic and converting between units.

Think of it this way. Let's say you had a database filled with the daily average temperatures for various cities. The temperatures are all in Celsius. You are asked to find all the cities whose average temperature is between 70 and 75 Fahrenheit. How would you go finding those cities if you aren't allowed to modify the temperature column?

In the case of your specific problem, instead of temperature you are dealing with time. The database is using a unit of time measured in days since some reference date, while you are trying to perform a query using a unit of measure based on a number of years, months, and days since a different reference date.

I highly recommend solving this problem with pen and paper first using just a small number of rows of representative data. Once you understand what you need to do using pen and paper, then start looking at how to implement it the naive way: read all the data into memory, do the updates in memory, and then only write back the changed data to the database. Once you figure that out, then look at how to write a SQL UPDATE command so that all the updates are performed by the database engine on the database server.