Converting Date/Datetime to SQL

Mr Edge 221 Reputation points

I have some Linq code which i converted to SQL but for some reason im getting different results so can only assume its the date conversion i have misunderstood (i removed all the other fields until i added the date).

The Linq code is

&& (og.Start == null || DateTime.Now.Date >= og.Start.Value.Date)

&& (og.End == null || DateTime.Now.Date <= og.End.Value)

&& og.EndDate > og.StartDate

My conversion for this is (missing other declarations for brevity)

declare @currentDate = GETDATE()

AND Start is not null
OR @currentDate => Start

AND End is not null
OR @currentDate =< End

AND End > Start

Is there something ive misconverted?

An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,433 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,455 questions
{count} votes

1 answer

Sort by: Most helpful
  1. P a u l 9,896 Reputation points

    You have parenthesis around those OR'd expressions in LINQ. You'll need to make sure they're in your SQL as AND has a higher precedence than OR (i.e. AND is evaluated first):

    declare @currentDate = GETDATE()
    AND (Start is null
    OR @currentDate => Start)
    AND (End is null
    OR @currentDate <= End)
    AND End > Start

    Edit: Also you've written Start is not null rather than Start is null (same for End).

    You're also comparing two dates in your LINQ for your Start date, but you're comparing a date-time with a date in your SQL. If you want to replicate this then you'll need to convert/offset your Start to make the SQL consistent:

    1 person found this answer helpful.
    0 comments No comments