12

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?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
iCurious
  • 8,161
  • 7
  • 28
  • 46
  • its of no use. i have tried both of urs answers. but the datatype i have set for the date is datetime. another thing is that i cann't use static value like '6/01/2012' because i want to set the timetable of roasters on daily basis and for this i want daily current date. i have also used DAY(date) = Day(getdate() but it's not working either. and retrieves null value for timeid, but i have 2 records already in it 1 have 3/6/2012 and the other one has 2/6/2012 date and records – iCurious Jun 03 '12 at 16:22
  • 3
    Are you sure "3/6/2012" is June 3 and not March 6? – Aaron Bertrand Jun 03 '12 at 16:43
  • 1
    You should also avoid using reserved (and very vague) words like `date` as column names. `created_date` is longer but it avoids a reserved keyword and better describes the data in that column. – Aaron Bertrand Jun 03 '12 at 17:08
  • possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) – Aaron Bertrand Jun 03 '12 at 18:32

1 Answers1

30

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • +1 I like to use CONVERT(DATE, x) to truncate off the time part, though. – usr Jun 03 '12 at 16:12
  • @usr I don't know that the OP can use `DATE` as no version of SQL Server was specified. When in doubt I always try to remember to use the lowest common denominator (this will work on 2000 and 2005 right on through 2012). – Aaron Bertrand Jun 03 '12 at 16:18
  • @AaronBertrand +1. Out of interest, is there any advantage to using `CURRENT_TIMESTAMP` over `GetDate()`? – Bridge Jun 03 '12 at 17:13
  • 1
    @Bridge just my preference since it is ANSI standard whereas `GETDATE()` is not. When there are no other differences (real or perceived), I try to stick to the standard. In this case I can only do so if I want to use the local date/time, since they didn't provide an equivalent for UTC (in which case I use `SYSUTCDATETIME()`). – Aaron Bertrand Jun 03 '12 at 17:17
  • @AaronBertrand they gave me some direction and rest of it i done my self. i don't know why the sqlserver giving some crazy answer but the query i made worked for me. i really appreciate all of your efforts. the query was SELECT timeId FROM table_roaster_time_table WHERE (MONTH(CAST(CONVERT(varchar(8), table_date, 112) AS date)) = DAY(CAST(CONVERT(varchar(8), GETDATE(), 112) AS date))) AND (user_id = @user_id)) – iCurious Jun 03 '12 at 19:01
  • 2
    @waqar that is the absolute worst way you can do this. Your problem was actually more related to the fact that you stored 3/6/2012 and thought that was June 3rd when in fact it was March 6th. You need to store dates correctly and you also need to heed the advice that converting your date to a varchar is the least efficient way to do what you're trying to do - did you read the possible duplicate? Also how on earth do you expect that query to ever make sense? Where MONTH(stored date) = DAY(today)? I'm so confused why you think that is getting the right results. – Aaron Bertrand Jun 03 '12 at 19:02
  • @AaronBertrand thanks thanks thanks that was my mistake where i was doing it wrong. actually it happens when you are from different country and the type of date you are giving is not the default date what VS accepts. anyways thanks again – iCurious Jun 03 '12 at 19:04
  • @waqar please think about this for a moment. Why do you think MONTH([mydate]) = DAY(GETDATE()) is giving you the "right" answer? Because *you stored March 6, not June 3.* Again, I urge you to think about this - if you don't, you'll be back tomorrow asking why your data from April 6th is not showing up. – Aaron Bertrand Jun 03 '12 at 19:06
  • @waqar if you believe that comparing months to days will give you the right result you maybe should not be programming. You are just jiggling things around until they seem to work. (But they don't for all cases). Programming by coincidence is not a valid strategy. – usr Jun 03 '12 at 19:07
  • @AaronBertrand i am not a bad programmer but today it was my bad luck that i was stuck in such stupid position. – iCurious Jun 03 '12 at 19:14
  • @waqar What does that have to do with "solving" the problem with a query that is both inefficient **and** incorrect? (P.S. I didn't call you a bad programmer - I just urged you to think about how you've "solved" the problem.) – Aaron Bertrand Jun 03 '12 at 19:14
  • @AaronBertrand ok i will try my level best not to do such silly mistakes next time. thanks for your help and advice – iCurious Jun 03 '12 at 19:22