Retrieving All Rows Created Today

Question

I cannot get all the lines created today. I used several functions like getdate() , Cast , Convert , etc., but all in vain.

This is my main request:

 SELECT timeId FROM table_roaster_time_table WHERE (user_id = @user_id) AND (DATEDIFF(d, date, GETDATE()) = 0) 

I want to get timeId from table table_roaster_time_table , where userid will be provided, and date today.

How to do it?

Answer 1

To be able to use the index in the [date] column (even if it does not exist today, it may in the future), try:

 AND [date] >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)) AND [date] < DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)); 

If you use SQL Server 2008 or higher, you can do something similar to shorten the code, but use the [date] index if it exists:

 AND CONVERT(DATE, [date]) = CONVERT(DATE, CURRENT_TIMESTAMP); 

EDIT

Since you seem to be confused why 3/6/2012 is March 6th rather than June 3rd, I can also assume that instead of manually inserting double-digit dates into the database, such as '3/6/2012' , you make a default column, for example:

 ALTER TABLE dbo.table_roaster_time_table ALTER COLUMN [date] DATETIME NOT NULL; ALTER TABLE dbo.table_roaster_time_table ADD CONSTRAINT df_date DEFAULT (CURRENT_TIMESTAMP) FOR [date]; 

If you are going to embed date literals, then at least use a safe and unambiguous format, such as YYYYMMDD :

 INSERT dbo.table_roaster_time_table([date]) VALUES('20120603'); 

Now there is no confusion.