Getting all rows created today

sql-server

Solution

In order to keep any chance of using an index on the `[date]` column (even if one doesn't 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're using SQL Server 2008 or better, you can do something like this to shorten the code but still make use of an index on `[date]` if one exists:

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

EDIT

Since you seem to be confused why `3/6/2012` is March 6th and not June 3rd, I might also suggest that instead of manually inserting ambiguous date literals like `'3/6/2012'` into the database, you make the column a default such as:

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're going to insert 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.

Problem

I am unable to get all the rows created today. I have used multiple functions like `getdate()`, `Cast`, `Convert`, etc. but all in vain. This is my basic query: ``` SELECT timeId FROM table_roaster_time_table WHERE (user_id = @user_id) AND (DATEDIFF(d, date, GETDATE()) = 0) ``` I want to get the `timeId` from the table `table_roaster_time_table` where `userid` will be provided and the date is today. How do I do this?

Original source