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!

Answer 1

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

• MSDN - GETDATE (Transact-SQL)
• MSDN - DATEDIFF (Transact-SQL)
• MSDN - DATEADD (Transact-SQL)
• Stackoverflow - CURRENT_TIMESTAMP vs GetDate()

Answer 2

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