MS SQL Comparison between two date values

I remember at some point I had some problem with this issue and I couldn't find something that suites a newbie.

The point that I was missing was that I couldn't pass the string representation of the date value to a query string. The key is to convert the string date to its equivalent datetime or smalldate .

For example imagine you'd like to select all Employees who where born on a specific date stored in a variable called myDate:

the BirthDay column in our Employee table has type smalldatetime

--------------

string myDate = @."06/06/1978";

...

SqlCommand comm = new SqlCommand("SELECT * FROM EMPLOYEE WHERE [BirthDay] = Convert(smalldatetime,'" + myDate + "', 103))";

...

--------------

Make suer that you don't miss single quotes around value of myDate in your query string.

For more info regrading the Convert function look at:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

SELECT *
FROM Employee
WHERE Birthday>= DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 0)
AND Birthday< DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 1)

@.myDate is datetime datatype.

|||

roozbehtk:

For example imagine you'd like to select all Employees who where born on a specific date stored in a variable called myDate:

This will work:

1declare @.MyDateDateTime23set @.MyDate ='2002-02-02'45select *6from MyTable7where DateOfBirth = @.MyDate8GO

Good luck.

|||

ndinakar:

SELECT *
FROM Employee
WHERE Birthday>= DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 0)
AND Birthday< DATEADD(DAY, DATEDIFF(DAY, 0, @.myDate), 1)

@.myDate is datetime datatype.

Using such functions (e.g. DateAdd & DateDiff) in the where clause..will decrease query performance.
No need for those two functions for the mentioned case.

|||My problem was that I had to use a specific class that took the WHERE clause as pure string. I couldn't parameterize my query string and yet I need a mechanism to pass my date.

What would be you're suggestion for that case?

|||

roozbehtk:

My problem was that I had to use a specific class that took the WHERE clause as pure string. I couldn't parameterize my query string and yet I need a mechanism to pass my date.

What would be you're suggestion for that case?

I did not understand you, can you please give more details?

|||

Hi roozbehtk,

you can try the following code:

..................................
SqlCommand cmd =new SqlCommand("select name from employee where birthday>=DateAdd(day,DateDiff(day,@.birth,0),0) and birth<DateAdd(day,DateDiff(day,@.birth,0),1)", con);cmd.Parameters.Add("birth", SqlDbType.DateTime, 10); cmd.Parameters["birth"].Value ="06/06/1978";
.............................

I've tested the code in my side, it works fine.

P.S If onlydate infomation is stored in your database (withouttimeinfo.) you can also try the solutionCS4Ever has suggsted . thanks

|||

If you don't want to use parameters, try this:

string cmdtext=test('1978-1-1') // string cmdtext is an excutable sql command. I also tested this solution in my box, it works fineprotected string test(string date) {string str = @."select name from student whre birth='{0}'";string ret =string.Format(str, date);return ret; }