Update SQL Database help

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
95
Programming Experience
Beginner
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

C#:
  String deletecommod = db.Query<String>("UPDATE REQUIREMENTSH SET REQUIREMENTSH.DELETED " + DateTime.Now.ToOADate() + " WHERE  REQUIREMENTSH.CREATIONDATE >= -5, GETDATE()") .FirstOrDefault();
 
First, its best practice to use parameters, and not cut into your strings like that.

You lost me at I have a datetime stored as double table format ?

Try put a little effort into your posts and please explain that properly. Your table is what stores all the relevant structures, and it's the structures we need to focus on inside the table, and not the table itself. What structure are you using for this
REQUIREMENTSH.DELETED field?

If you are working with dates, then use a date field, and datetime for date time fields respectively. See Date Functions in SQL Server and MySQL
 
The database was probably originally designed by a VB practitioner and they thought it to be natural to save date/time in VB's native format for datetimes: a double. That was before a more enlightened time...

Anyway, if you don't have any requirement for the database to continue to store doubles for dates, it's worth taking the one time hit to convert the data to use a more appropriate database native date/time format.
 
The issue is skydiver i cant convert it to a double
We dont manage the firebird database. Can i do anything to convert REQUIREMENTSH.DOUBLE to DateTime in order to update the table for records older than 5 days?
Thanks
 
Hi Sheepings
I have tried the following (see below) but the FromOADate is showing as an error. Do i need to put something within the ()? THanks
C#:
string deletecommod = db.Query<String>(" SELEC  UPDATE STOCKDET SET STOCKDET.QTY " + 0 + " WHERE STOCKHDR.RECEIVED " + DateTime.FromOADate() + " >= -30, GETDATE()").FirstOrDefault();
 
That is because FromOADate() takes a parameter. Did you even look at the documentation first before trying it out?
 
Hi Sheepings
its a console application tjhat runs runs the query and updates the DB.
I will check out the hyperlink thank you.
 
Forgive my stupidness. I am learning!
I understand how to convert it from a double figure i.e.
// using FromOADate() method
DateTime date2 = DateTime.FromOADate(657435.0);
But im stuck with how to do this parameter value from a database table if that makes sense.
 
You're welcome.

So you've resolved your problem with the .FromOADate() ?

If so, open a new topic about how to use parameters.
 
No i know how to run it from a double format but i'm confused with how to run a FromOADate to convert the table format for my query.
Example
FromOADate(STOCKHDR.RECEIVED)
STOCKHDR.RECEIVED Is the firebird DB table
 
I mean its working for a DateTime.FromOADate(657435.0);
but i need to run it against the database table, however its also a date/time so i suspect i will need to DATEDIFF in order to query it to only update if records are older than 30 days
 
Back
Top Bottom