0

In my Windows Form C# Application, I am inserting the record into the SQL server database. Along with other fields, I am also inserting both the current date and current time using the following format:

DateTime currentDate = DateTime.Now;
string SaleDate = currentDate.ToString("dd-MM-yyyy");

DateTime currentTime = DateTime.Now;
string SaleTime = currentTime.ToString("hh:mm:ss tt");

Which works fine and the records are inserted successfully with Date and Time format like this.

However, when I select records between two dates, I am unable to perform this operation. I am sure that there is no problem in this custom format of my date because I have edited the same format several times and even I have saved the date in its default format but still I am unable to select the target records (between two dates)

I am using the following select query in my application to select records from the view vAllSales which results in selecting either all the records or records whose SaleDate does not meet the specified filter criteria:

select * from vAllSales where SaleDate >= '20-04-2019' and SaleDate <= '30-04-2019'"

I have tried the following queries in my SQL server as well to inspect the cause:

1.

select * from vAllSales where SaleDate between '21-04-2019' and '09-05-2019'

The above query does not return any value. Like this

2.

select * from vAllSales where SaleDate >= '21-04-2019'

The above query select only a few records. Because 21-04-2019 is the initial SaleDate and the query should return all of the Sales but it only returns a few records, like this.

Is there any issue with the specified date format within my code? Or do I have to work on improving my queries? I need a more professional and reliable approach for this case.

Noor
  • 185
  • 2
  • 18
  • Could well be down to datetime format. What you probably want to do though is use parameters (which should solve it); taking plain text like that will leave you open to SQL injection. This link may help https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements – d219 Jun 16 '19 at 09:23
  • You saving date and time as a string, so query will treat dates in database as a string. Save them in the column of type DateTime – Fabio Jun 16 '19 at 09:24

2 Answers2

3

You should store your date and time as datetime2 objects in your database.

It will give you the benefits of:

  • better sorting: (your current string format does not allow a natural sort)
  • better performance; since your date and times are represented as numbers in stead of strings
  • easier to handle in code and queries: since it maps directly to a C# DateTime object.

For more info on database types see:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017

And perhaps:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql?view=sql-server-2017

If you absolutely must use a string to represent your date, make sure it's in the sort-able ISO format.

YYYY-MM-DD

due to the nature of this format it's lexicographical sort-able as a string type.

Stefan
  • 17,448
  • 11
  • 60
  • 79
  • The sortable ISO Format works for me. Instead of using the format `dd-MM-yyyy`, I used `yyyy-MM-dd` and it resolved the issue. – Noor Jun 16 '19 at 10:15
1

You are trying to apply logical operations to string values. What will here happen is lexicographical comparing. (left to right).

So you better use correct data types to store data in table.

But If you really want to store date as a string, you have to cast them to proper data types before comparison.

select * from vAllSales where  CAST(SaleDate AS DATE) >= '21-04-2019'

Keep note that ms sql server uses single quotes for pass datetimes. That doesn't mean those are strings or varchar

cdev
  • 5,043
  • 2
  • 33
  • 32