SQL date where clause

David Smith

14y4.1kSQL

I need help with the datetime where clause, I am normally doing this in Linq, but now I want to do this where clause into a sql query. Review below.

Basically I selecting records between plus or minus 60 days from current datetime now. I am using an access database for current project. I just need help with current where clause in green.

Select firstName, lastName, CreatedDateTime

FROM ExampleTable

Where CreatedDateTime >= DateAdd(""d"",Now(),-60) AND CreatedDateTime <= DateAdd(""d"",Now(),60)

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
If the query is within a C# string, then you can either:

1 . Use single quotes.

2. Use double quotes escaped with a backslash as Sukesh has done.

3. Use a verbatim string and double the double quotes:

string sqlDateFilter = @" CreatedDateTime >= DateAdd(""d"", -60, Now()) AND CreatedDateTime <= DateAdd(""d"", 60, Now())";
Vulpes14y ago
It's not much better but in C# (untested) you could do:

TempYear = DateTime.Now.AddMonths(-6).Year;
FiscalYearStart = "7/1/" + TempYear;
FiscalYearEnd = "6/30/" + (TempYear + 1);
StrFilter += "ServiceDateStart >= #" + FiscalYearStart + "# AND ServiceDateStart <= #" + FiscalYearEnd + "#";
David Smith14y ago
Is there a simpler way to write this logic in c sharp or an sql query
David Smith14y ago
Is there a simpler way to this logic, review below. I am using access database converting logic over to c sharp

If Month(Now()) < 7 Then
TempYear = Year(Now()) - 1
Else
TempYear = Year(Now())
End If
FiscalYearStart = "7/1/" & TempYear
FiscalYearEnd = "6/30/" & (TempYear + 1)
StrFilter = StrFilter & "ServiceDateStart >= #" & FiscalYearStart & "# AND ServiceDateStart <= #" & FiscalYearEnd & "#"
Sukesh Marla14y ago
string sqlDateFilter = " CreatedDateTime >= DateAdd(\"n\",-60,Now()) AND CreatedDateTime <= DateAdd(\"n\",60,Now())";

Try this

Check this is correct answer if it helped.
David Smith14y ago
Vuples should I be using single qoutes instead of double qoutes, when I use double qoutes, I am getting an error. Look at string below.

string sqlDateFilter = " CreatedDateTime >= DateAdd(''d'',Now(),-60) AND CreatedDateTime <= DateAdd(''d'',Now(),60)";
Vulpes14y ago
Try:

SELECT firstName, lastName, CreatedDateTime
FROM ExampleTable
WHERE CreatedDateTime >= DateAdd("d", -60, Now()) AND CreatedDateTime <= DateAdd("d", 60, Now())