SELECT Query: I want Date part only from DateTime For Filtering rows

Question

I have table xxx (,,,,,); among these one column has DateTime datatype (say Login_date)

Login_date has values like: " 27-Nov-13 3:12:07 PM "

My Problem is that

i want to select the rows of table xxx where Login_date is " 27-Nov-13 " i am getting error when executing a query like this

DateTime dt=new DateTime();
dt=DateTime.Now;
Command Query is:      " SELECT * FROM xxx WHERE (Login_Date=@date)  "
cmd.Parameters.add("@date",dt);

This is not selecting any rows from table xxx.

This is not working because of difference in Time.

& here i am using MYSQL data base Please bring me out of this problem.

Ankit · Answer 1 · 2013-11-27T11:07:51.890

1

pls try this,

 var dt=DateTime.Now.ToString("dd-MMM-yy");

edited Nov 27 '13 at 11:07

answered Nov 27 '13 at 10:56

Ankit

207
1
11

See i want it to change in SQL query, Not in Program..! – Rahul Uttarkar Nov 27 '13 at 11:07
**NO** If he has a date/time type in the database, changing the application variable to a string won't help. In fact, depending on a number of settings, this may get completely wrong results. – Clockwork-Muse Nov 28 '13 at 11:02

score 0 · Answer 2 · answered Nov 27 '13 at 10:26

0

Try this:

dt.ToString("dd-MMM-yy")

So when adding your parameters:

cmd.Parameters.add("@date",dt.ToString("dd-MMM-yy"));

answered Nov 27 '13 at 10:26

dav_i

27,509
17
104
136

its fine, but how do u write in command query..? It will Not Work.. I already Tested. – Rahul Uttarkar Nov 27 '13 at 10:28
it is not selecting any rows because there is time difference though dates are same. – Rahul Uttarkar Nov 27 '13 at 10:32
Please see my [other answer](http://stackoverflow.com/a/20240165/1185053) – dav_i Nov 27 '13 at 19:50
**NO**. If he has a date/time type in the database, changing the application variable to a string won't help. In fact, depending on a number of settings, this may get completely wrong results. – Clockwork-Muse Nov 28 '13 at 11:03

score 0 · Answer 3 · edited Nov 27 '13 at 16:41

0

Try where convert(datetime, cast(login_date as varchar(12)),103)='27-Nov-13'

I always find the following page on DateTime an excellent resource: sql server datetime workbench

edited Nov 27 '13 at 16:41

Christian Phillips

18,399
8
53
82

answered Nov 27 '13 at 10:29

Lima

1,203
3
22
51

Using SQL server this may Execute.... But i need it in MY-SQl server.. – Rahul Uttarkar Nov 28 '13 at 09:50
**NO.** Converting to a string is not a good option. Not only will this ignore any indices on the column, but it has a chance to go wrong in other ways as well. – Clockwork-Muse Nov 28 '13 at 11:07

score 0 · Answer 4 · answered Nov 27 '13 at 10:29

0

Try this:

DateTime dt = DateTime.Now;

var cmd = new SqlCommand("SELECT * FROM xxx WHERE (DATEADD(dd, 0, DATEDIFF(dd, 0, Login_Date)) = DATEADD(dd, 0, DATEDIFF(dd, 0, @date)))");
cmd.Parameters.add("@date", dt);

this way we are comparing only date without time.

answered Nov 27 '13 at 10:29

gzaxx

17,312
2
36
54

er, doing this kind of date math will cause the optimizer to not use any index on that column, meaning the query will take longer than it should. – Clockwork-Muse Nov 28 '13 at 11:09

score 0 · Answer 5 · answered Nov 27 '13 at 10:31

0

Try this:

DateTime dt=DateTime.Today; // Note Today, not Now
Command Query is:      
"SELECT * FROM xxx WHERE Login_Date>=@date AND Login_Date < DATEADD(d,1,@date)"
cmd.Parameters.add("@date",dt);

Using a date range in your query like this can be more efficient than casting to DATE because it enables you to take advantage of any index there may be on the DATETIME column.

answered Nov 27 '13 at 10:31

Joe

122,218
32
205
338

This may work in SQL server, But i wanted in MYSQL server.. Please Help; – Rahul Uttarkar Nov 28 '13 at 09:51

score 0 · Answer 6 · answered Nov 27 '13 at 10:33

0

try below query,

SELECT * FROM xxx WHERE (convert(date,Login_Date,101))=convert(date,@date,101)

answered Nov 27 '13 at 10:33

RobertKing

1,853
8
30
54

Converting the column this way will cause the optimizer to ignore any indices, meaning the query will take longer than it needs to. – Clockwork-Muse Nov 28 '13 at 11:09
working fine(CAST & CONVERT) in select query but not in Update query.. – Rahul Uttarkar Nov 29 '13 at 04:39

score 0 · Answer 7 · answered Nov 27 '13 at 10:33

This should work...

CREATE TABLE #tabledates ( dt DATETIME )

INSERT  INTO #tabledates
        ( dt )
VALUES  ( '2013-11-27 10:30:07' )

INSERT  INTO #tabledates
        ( dt )
VALUES  ( '2013-11-27 10:30:07' )

INSERT  INTO #tabledates
        ( dt )
VALUES  ( '2013-11-28 10:30:07' )

INSERT  INTO #tabledates
        ( dt )
VALUES  ( '2013-11-28 10:30:07' ) 

SELECT  *
FROM    #tabledates
WHERE   DATEADD(dd, 0, DATEDIFF(dd, 0, dt)) = '2013-11-28'

So, in your case, you would have...

DateTime dt=new DateTime();
dt=DateTime.Now;
Command Query is:"SELECT * FROM xxx WHERE 
                    DATEADD(dd, 0, DATEDIFF(dd, 0, Login_Date))=@date"
cmd.Parameters.add("@date",dt);

Doing this (converting/doing date math) will completely invalidate the use of any indices on that column for a search, meaning this is going to take longer than it should. — Clockwork-Muse, Nov 28 '13 at 11:06

score 0 · Answer 8 · answered Nov 27 '13 at 10:40

0

Okay, try this as your command query:

SELECT [t0].[Column], [t0].[Date]
FROM [Table] AS [t0]
WHERE CONVERT(DATE, [t0].[Date]) = @p0

And use:

var dt = DateTime.Today;

answered Nov 27 '13 at 10:40

dav_i

27,509
17
104
136

This will work in SQL-Server... But i need in **MY-SQL** server... Please Try some.. – Rahul Uttarkar Nov 28 '13 at 09:53
He shouldn't need to convert the column to get the answer. Among other things, this will completely invalidate the use of any index during query execution, meaning it's going to take longer than it should. – Clockwork-Muse Nov 28 '13 at 11:04

score 0 · Answer 9 · answered Nov 27 '13 at 11:11

0

Please Try it

SELECT * FROM xxx  WHERE (CONVERT(varchar(10), Login_Date,110)='27-Nov-13')

answered Nov 27 '13 at 11:11

code save

1,054
1
9
15

Using sql server this may execute.. But i am using MYSQL server.. – Rahul Uttarkar Nov 28 '13 at 09:49
**NO.** Converting to a string is _not_ a good option. Not only will this ignore any indices on the column, but it has a chance to go wrong in other ways as well. – Clockwork-Muse Nov 28 '13 at 11:07

Clockwork-Muse · Answer 10 · 2013-11-28T11:47:57.393

0

@Joe's answer is correct, but unfortunately is written for SQL Server.
Here's a version for MySQL:

SELECT Login_Date
FROM tabledates
WHERE Login_Date >= DATE(@date)
      AND Login_Date < DATE_ADD(DATE(@date), INTERVAL 1 DAY)

(working SQL Fiddle example, only with an explicit timestamp instead of the replacement variable)
Will use an index, if available.

edited Nov 28 '13 at 11:47

answered Nov 28 '13 at 11:23

Clockwork-Muse

12,806
6
31
45

score 0 · Answer 11 · answered Jan 03 '14 at 16:59

Having an index on a date/time variable is not the best practice. I'd suggest that you create another field in the table, which will have only the date value, and then it would be easy to execute your own script and that will retrieve to you exactly what is needed. In case this solution is not doable; I'd suggest that you use:

SELECT *
FROM xxx
WHERE Login_Date BETWEEN Date1 AND Date2

Where Date1 will be the date of when you want the Login attempts to show (November 27, 2013) and Date2 will be the next day (November 28, 2013)

N.B.: The most important thing to keep in your mind, in the second example, that you make sure that the times are 00:00.000, so no attempts would be included from the next day

SELECT Query: I want Date part only from DateTime For Filtering rows

11 Answers11