0

I am debugging a query that is getting built in C# maybe with EntityFrameWork - not sure - but it doesn't return any records although it should. The query has some DateTime fields and they are like this:

personStartDate   {4/3/2013 12:00:00 AM}

The value for Date is getting from user interface date picker but I guess it also defaults the time part to 12:00:00 AM somehow.

Then I go to SQL Server and view the table rows and values on their datatime field data looks like this example: '2013-04-23 09:20:38.897'

Do you see any obvious problem right there? Also I am trying to take the generated SQL from my breakpoint in C# and post it to SQL Server to see what does it return so for a value like {4/3/2013 12:00:00 AM} I am replacing it with 2013-04-03 12:00:00.000 Is that even correct?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

3 Answers3

5

Formatting is irrelevant. Internally it won't be in a text format at all, and I'd hope that the query doesn't end up sending the query value to the database as text either.

If you're only interested in the date part, you need to say that in the query:

where foo.Start.Date == personStartDate

(for example - we don't know what your query looks like).

If your field in the database is logically just a Date but is currently a DateTime, you should consider changing your schema to match your logical data model. It'll make things simpler.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
2

If you create a DateTime using DateTime.Today then the time part will default to midnight. That is what the date picker is doing.

Your database contains a time portion too. If that is incorrect you can convert it in sql: Best approach to remove time part of datetime in SQL Server

2013-04-03 12:00:00.000 is noon is 12:00:00 PM. You want 12:00:00 AM which is midnight. You should use 2013-04-03 00:00 in your test or 2013-04-03 or '3 April 2013'. Again, the time portion will default to 00:00.

To get the query to work in c# with the "bad" data in the database, make the query less precise by doing "less than tomorrow more than or equal to today" rather than "equals". Or make the database more precise by dropping the time part - then you can use "equals". If you are using Entity Framework and Linq-to-Entities you may need to use EF's DbFunctions

Ideally you should track down the inserts and updates that are setting the time in the database and stop that happening, then fix the existing data. Then you could change the data type in the database from DateTime to Date.

Community
  • 1
  • 1
Colin
  • 22,328
  • 17
  • 103
  • 197
  • just a few minutes before I leave work today I actually tried this approach of ""less than tomorrow more than today" just by try and error and getting lucky! and it DID work... Not sure why?! but yeah it worked ... I did a mistake that I should fix on Monday tho, I made it less than or EQUAL to tomorrow OR more that or equal to today –  Nov 28 '13 at 06:13
1

A Sql Server DateTime value is a pair of 32-bit integers. The first is the count of days from the SQL Server calendar's epoch (1 Jan 1900 00:00:00.000); the second is the count of milliseconds since start of day.

The string representation of that is dependent on (A) the default language setting for your SQL Server instance, (B) the current language setting for the session, (C) the current set dateformat setting, and probably a few other options I've forgotten.

If you care about the string representation, explicitly convert it to a string using convert(varchar(X),your-datetime-value-here,style) using the style of your choice.

Note that SQL Server Date and DateTime values are converted to/from System.DateTime values by the runtime.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135