Convert database column format in SqlCommand

Question

In a SELECT command to my SQL Server 2005 database, I need to convert the OrderDelDateTime column (which is in datetime format) to a date format so that I can select all rows with today's date, regardless of the time.

Here is what I've got:

cmd = New Data.SqlClient.SqlCommand("SELECT * FROM [Orders] where [OrderDelDateTime] = '" + Now.Date + "'", conn)

Obviously this returns zero results because 11/30/2012 5:30:00 PM (in db) doesn't equal Now.Date (11/30/2012).

I'm using VB.Net.

Since you're still on SQL Server 2005, converting the `DATETIME` in the database table to look at only the date portion is a bit involved - [see this other SO question and its answers](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) on how to do this. Once you've converted your `OrderDelDateTime` to only date, you can then compare it to `Now.Date` — marc_s, Nov 30 '12 at 20:18

Farhan · Accepted Answer · 2012-11-30T21:33:28.493

4

Make your query like this:

select *
from Orders
where OrderDelDateTime between @Date1 and @Date2

Then from C#, pass the following two parameters:

cmd.Parameters.AddWithValue("@Date1", Now.Date);
cmd.Parameters.AddWithValue("@Date2", Now.Date.AddDays(1).AddMilliseconds(-1));

SQL Fiddle: http://sqlfiddle.com/#!3/92105/1

edited Nov 30 '12 at 21:33

answered Nov 30 '12 at 19:39

Farhan

2,535
4
32
54

I like it - I should have come with that "out of the box" thinking. Thanks. – user1375002 Nov 30 '12 at 20:27
This will also select records with the next day if the time is 00:00:00 – George Nov 30 '12 at 20:32
Dim dteYesterday As DateTime = Now.Date.AddDays(-1) & " 11:59:59 PM" Dim dteTomorrow As DateTime = Now.Date.AddDays(+1) & " 12:01:01 AM" cmd = New Data.SqlClient.SqlCommand("SELECT * FROM [Orders] where [OrderDelDateTime] > '" + dteYesterday + "' and [OrderDelDateTime] < '" + dteTomorrow + "'", conn) – user1375002 Nov 30 '12 at 20:58
@user1375002: I thought you were doing it in C# instead of VB.NET. Btw, you don't need to append times for SQL Query. If you just add a day, SQL will always assume its 12AM if there is no time with a date. – Farhan Nov 30 '12 at 21:05
1

@user1375002: "12:01:01 AM" should be "12:00:00 AM". But, like Muhammad mentioned, you do not really need to specify time. All you have to do is check whether payment is greater or equal then today without mentioning any time and less then tomorrow without mentioning any time. Muhammad's original answer is not accurate because it will also return any order with tomorrow's date if the time is 00:00:00 – George Nov 30 '12 at 21:11
1

@George: I see your point. Good catch. I updated the code above to subtract 1 millisecond. – Farhan Nov 30 '12 at 21:32

score 1 · Answer 2 · answered Nov 30 '12 at 20:44

1

cmd = New Data.SqlClient.SqlCommand(String.Format("SELECT * FROM [Orders] where [OrderDelDateTime] >= {0} and  [OrderDelDateTime] < {1}", Now.Date, Now.Date.AddDays(1)), conn)

answered Nov 30 '12 at 20:44

George

2,165
2
22
34

Convert database column format in SqlCommand

2 Answers2