Sql query with the current date

Question

I've got a simple query where I want to put the current date

var query = @"
    SELECT trainid, trainnum
    FROM trains 
    WHERE CONVERT(varchar(10), trainstartdate, 104)=" + 
    " " + 
    // so that matches the '104' format
    String.Format("{0:dd.MM.YYYY}", DateTime.Now) +                          
    " " +
    "ORDER BY trainnum";

But when running I get the error message:

Cannot call methods on numeric. .Net SqlClient Data Provider

How do I specify current date the right way? Thanks!

Using GETDATE()

Effect: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Using {0:dd.MM.yyyy}

Effect: none

Using CONVERT(varchar(20), GetDate(), 104)

Effect: that works!

Thanks!

You should use [SqlParameters](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx) with [SqlDbType.DateTime](http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx) instead. — Tim Schmelter, Feb 14 '12 at 09:41
Whats wrong with my answer ? I just want to know to get better. — dknaack, Feb 14 '12 at 09:58
@dknaack look at my edit (`Using GETDATE`). In my case If I use just a plain `GetDate()` call then I have the error message. Instead result of `GETDATE()` should be converted to format '104'. — lexeme, Feb 14 '12 at 10:33
@helicera please check out my updated answer and compare the performance. — dknaack, Feb 14 '12 at 13:59

score 9 · Answer 1 · edited Jun 20 '20 at 09:12

9

Description

I would not convert to a varchar and doing string comparrisson. The performance is much better if you compare trainstartdate using the >= and <.

You can use the T-SQL getDate() method to get the current date.

getDate() returns the current datetime with the time. 2012-02-14 14:51:08.350

DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) return only the current date. `2012-02-14 00:00:00.000

DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE())) returns only the date of tomorow. 2012-02-15 00:00:00.000

Sample

var query = @"
SELECT trainid, trainnum
FROM trains 
WHERE trainstartdate >=
-- today
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
AND trainstartdate < 
-- tommorow
DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))
ORDER BY trainnum"

Note: If you want to be ANSI compliant, CURRENT_TIMESTAMP does the same.

More Information

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 14 '12 at 09:40

dknaack

60,192
27
155
202

1

+1 - Also avoids the terrible stronc comparison that force a table scan in the OP query. – TomTom Feb 14 '12 at 09:42
1

+1. Also, if you want to be ANSI compliant, CURRENT_TIMESTAMP does the same http://stackoverflow.com/questions/186572/retriving-date-in-sql-server-current-timestamp-vs-getdate – StuartLC Feb 14 '12 at 09:44
@nonnb nice! My answer is extended. – dknaack Feb 14 '12 at 09:50
-1 this gives a different result from the OP's attempt: instead of the rows for the current date, it gives the rows who's trainstartdate is right now. – Joe Feb 14 '12 at 12:56
@Joe good comment! My answer is updated. The best solution is not to convert to a varchar. Using the `between` statement will give the best performance. Try it. Thank you! – dknaack Feb 14 '12 at 13:58
@dknaack, Much better but still not quite right. SQL Server's BETWEEN operator specifies an inclusive range, so your query will return rows with tomorrow's date. Better and clearer to use ">= [today] AND < [tomorrow]". – Joe Feb 14 '12 at 14:12
@Joe, oh thats true. Even if we talking about 3 ms on `DateTime` and 100 nanoseconds on `DateTim2` it matters. My answer is updated. Thank you very much. – dknaack Feb 14 '12 at 14:21

score 2 · Answer 2 · answered Feb 14 '12 at 09:39

2

GETDATE() is all you need...

answered Feb 14 '12 at 09:39

Tieson T.

20,774
6
77
92

score 2 · Answer 3 · edited Apr 13 '17 at 18:11

2

I think

String.Format("{0:dd.MM.YYYY}", DateTime.Now);

is returning the date with a dot, which makes SQL consider it as a number.

Try using

String.Format("{0:MM/dd/yyyy}", DateTime.Now);

with a / instead.

edited Apr 13 '17 at 18:11

philipvr

5,738
4
32
44

answered Feb 14 '12 at 09:40

Diego

34,802
21
91
134

Dennis Traub · Accepted Answer · 2012-02-14T13:43:51.713

2

var query = @"
SELECT trainid, trainnum
FROM trains 
WHERE CONVERT(varchar(10), trainstartdate, 104)=
CONVERT(varchar(20), GetDate(), 104)
ORDER BY trainnum";

edited Feb 14 '12 at 13:43

answered Feb 14 '12 at 09:42

Dennis Traub

50,557
7
93
108

The two queries are not equivalent. The first returns rows whose start date is sometime today; the second returns rows whose start date is right now (current date + time of day). – Joe Feb 14 '12 at 13:20
@Joe that's true. I removed the second query. – Dennis Traub Feb 14 '12 at 13:44

score 1 · Answer 5 · answered Feb 14 '12 at 09:40

1

Change the format pattern of YYYY to small-case letters

{0:dd.MM.yyyy}

answered Feb 14 '12 at 09:40

Mohammed Swillam

9,119
4
36
47

score 1 · Answer 6 · answered Feb 14 '12 at 13:09

You need to be aware that GETDATE() returns the current date and time of day, not only today's date.

If you want to return rows matching today's date, you need to extract the date part. There are a number of ways to do this - e.g. with SQL Server 2008 you can use the DATE data type, but one general way that works with earlier versions of SQL Server is the following:

CONVERT(DATETIME, convert(VARCHAR(10),getdate(),101) )

You can then use the query:

SELECT trainid, trainnum   
FROM trains    
WHERE trainstartdate = CONVERT(DATETIME, convert(VARCHAR(10),getdate(),101) )

which will work provided you are sure that the date/time in the trains.trainstartdate column is a date only (time of day = 0).

If trainstartdate contains the start date/time, you can get all of today's trains as follows:

SELECT trainid, trainnum   
FROM trains    
WHERE trainstartdate >= CONVERT(DATETIME, convert(VARCHAR(10),getdate(),101) )
AND trainstartdate < DATEADD(dd,1, CONVERT(DATETIME, convert(VARCHAR(10),getdate(),101) ))

By doing it like this rather than converting to a string, you will take advantage of any index there may be on the trainstartdate column.

score 0 · Answer 7 · answered Feb 14 '12 at 09:40

0

Try this .. YYYY should be small letter yyyy

String.Format("{0:dd.MM.yyyy}", DateTime.Now)

answered Feb 14 '12 at 09:40

Thit Lwin Oo

3,388
3
20
23

Sql query with the current date

7 Answers7

Description

Sample

More Information