Update SQL Database help

tdignan87

Member
Joined
Jul 8, 2019
Messages
18
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();
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
345
Location
UK
Programming Experience
10+
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
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
365
Location
Virginia Beach, VA
Programming Experience
10+
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.
 

tdignan87

Member
Joined
Jul 8, 2019
Messages
18
Programming Experience
Beginner
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
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
345
Location
UK
Programming Experience
10+
@tdignan87 Try DateTime.FromOADate()?

@Skydiver, I haven't done any VB in years. I think the brain cells responsible for holding data that old has burned out long ago. Good call :)
 
Top Bottom