datetime format

Question

User1248258412 posted

1. how to save text fields data to yyyy/mm/dd hh:mm format in table.

2. how to calculate how many hours difference in that format yyyy/mm/dd hh:mm when compare 2 fields?

Thanks..

Answer 1

User-1716253493 posted

Use DateTime fields only

Use parameterized query to pass datetime value without worrying the format.

string query = "INSERT INTO TBL (starttime,endtime) Values (@starttime, @endtime)";
cmd.Parameters.AddWithValue("@startime", DateTime.Now); //pass datetime value
cmd.Parameters.AddWithValue("@startime", DateTime.Now.AddDays(1));

//you can use DateTime.Parse or DateTime.ParseExact to convert Text to DateTime value

Convert text to datetime in C#/VB before pass it through parameter

To calculate 2 datetime field use DateDiff(hh, field1, field2)

http://www.w3schools.com/sql/func_datediff.asp

Answer 2

User1208776063 posted

1. how to save text fields data to yyyy/mm/dd hh:mm format in table.

You haven't specified the database that you are using to store the data. I would recommed using a DateTime column to store this type of string. MySQL database provides a way to specify the format that you can save(I read this somewhere, I'm not an expert in MySql Database).  If you are using just a varchar field to store a date string in specific format, you would have to parse column value to date value for comparison(I've noticed date comparisons are marginally faster than just comparing 'date string' to 'date string').  It also depends on the purpose of this column. But, I would recommend saving data in a dateTime column and handling the formatting in front end/queries(TO_CHAR in Oracle or CONVERT(VARCHAR) in SQL server)

2. how to calculate how many hours difference in that format yyyy/mm/dd hh:mm when compare 2 fields?

If you are comparing two datetime values, you can use DateDiff in SQL Server, different ways in Oracle. It depends on the database that you are using.

Answer 3

User753101303 posted

Hi,

In addition :

- you don't save a datetime to a table using a particular format. You just save the value and it is stored regardless of any format. A format is used only when you convert this value to or from a string (ie when you show the value or read user input).

- similarly just use the available functions (likely DATEDIFF not sure what is your db or if you are using this db side or if you wanted something on the C# side)

For now it looks like you want to store datetimes using a string that would have a particular format. I would strongly discourage doing so.