Sql query with current date

Question

I have 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 I start, I get an error message:

Cannot call methods on numeric. .Net SqlClient Data Provider

How to indicate the correct date of the current date? 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

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

Answer 2

Description

I would not convert to varchar and do string matching. Performance is much better when comparing trainstartdate with >= and < .

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

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

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

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

Example

 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 compatible with ANSI, CURRENT_TIMESTAMP does the same.

Additional Information

• MSDN - GETDATE (Transact-SQL)
• MSDN - DATEDIFF (Transact-SQL)
• MSDN - DATEADD (Transact-SQL)
• fooobar.com/questions/42308 / ...

Answer 3

GETDATE () is all you need ...

Answer 4

I think,

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

returns a date with a dot, which causes SQL to consider it a number.

Try using

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

with / .

Answer 5

Change the YYYY format template to small letters

 {0:dd.MM.yyyy} 

Answer 6

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

If you want to return strings matching today's date, you need to extract a portion of the date. There are several ways to do this — for example, with SQL Server 2008 you can use the DATE data type, but one common way to work with earlier versions of SQL Server is as follows:

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

Then you can use the query:

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

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

If trainstartdate contains a 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 this, rather than converting to a string, you will use any index that may be in the trainstartdate column.

Answer 7

Try it. YYYY should be a small letter yyyy

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