Tuesday, November 21, 2006

Get Date from DateTime in SQL Server 2005

Since I spent some time searching for that and I realized that it's not easy/fast to find the solution, I post it here.

Configuration.
SQL Server 2005 with a table that contains at least a DateTime column.
What you want to do, is to check a given date('21/11/2006') with the values of the DateTime column. The problem is that the column contains DateTime (which means Date and Time) data.

This problem could occur (occurred to me) when you implement a flight booking system. You have to save the flights with DateTime because you need the time as well, but when the user searches for flights, you need to create an sql command that checks the date entered by the user with the ones in the database, an ignore the time.

So, what I used is (almost) the sql below
 
string sql = "SELECT * FROM Flight where "+
"(CONVERT(char(10), DepartureTime, 103)='" + depDate + "')";

I recommend you to read the msdn article about the Convert function in order to understand what it's parameters do..

7 comments:

Zafiris said...

Ah, little Stavros is becoming a man... We faced similar problems in our payroll management software, and found another solution. You can ask Lipis or RockOrDie (I am not telling you)

Zafiris said...

...what I can tell you is, that if you still know how to do Delphi when you come back, I will offer you a job.

Zafiris said...

... for a third of the money they would probably pay you in Denmark...

Stavros said...

Why don't you tell me? :'(

..what about for half of the money they pay me in Denmark?

I will have a full time job from April here.. I will let you do the math :)

Lipis said...

CAST(FLOOR(CAST(DepartureTime AS FLOAT)) AS DateTime)

Πάρε να 'χεις...!

rockordie said...

Και επειδή δεν θέλει κόπο αλλά θέλει τρόπο μπορείς απλά να κάνεις

WHERE
(DepartureTime >= '11/22/2006' AND
DepartureTime < '11/23/2006')

Όπως είδες, δεν ανακάλυψες την Αμερική!!!

Stavros said...

Thanks RockOrDie..

I like your solution..
and even though I thought it might not work, it is actually working.. :)