Query Decimal Date field for Yesterday's Records
in [Programming SQL]
|
From: jtibbs on 9 Mar 2010 03:37 I have a table in which the records contain a Date (DDate) that is stored as a Decimal value (IE. 30710). I need to SELECT from that table only the records WHERE DDate is equal to yesterday (Current Date - 1) and I am having a heck of a time figuring out how to do this. Any help would be appreciated! Thanks!
From: Robert Lakinski on 9 Mar 2010 05:52 maybe this can help: Select .. from .. where Cast (DDate as int) = Cast(GetDate()-1 as int) Robert "jtibbs" <u58639(a)uwe> wrote in message news:a4bddfc978a5e(a)uwe... > I have a table in which the records contain a Date (DDate) that is stored > as > a Decimal value (IE. 30710). I need to SELECT from that table only the > records WHERE DDate is equal to yesterday (Current Date - 1) and I am > having > a heck of a time figuring out how to do this. Any help would be > appreciated! > Thanks! >
From: Dave Ballantyne on 9 Mar 2010 07:56 Careful of these SARG's. This will more than likely cause tablescans Dave Ballantyne http://sqlblogcasts.com/blogs/sqlandthelike/ Robert Lakinski wrote: > maybe this can help: > > Select .. > from .. > where Cast (DDate as int) = Cast(GetDate()-1 as int) > > Robert > > "jtibbs" <u58639(a)uwe> wrote in message news:a4bddfc978a5e(a)uwe... >> I have a table in which the records contain a Date (DDate) that is >> stored as >> a Decimal value (IE. 30710). I need to SELECT from that table only the >> records WHERE DDate is equal to yesterday (Current Date - 1) and I am >> having >> a heck of a time figuring out how to do this. Any help would be >> appreciated! >> Thanks! >>
From: Plamen Ratchev on 9 Mar 2010 09:57 What date is represented by the number 30710? The appropriate way is if possible to alter the column and change the data type to date/time data type. If not possible then you can create computed column that converts the value to date/time (and you can create an index on the column). Or use a view. Then the task is trivial. Here is example with explicit casting: SELECT <columns> FROM Table WHERE CAST(ddate AS DATETIME) >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 1, 0) AND CAST(ddate AS DATETIME) < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0); Note in the example above most likely an index on the ddate column cannot be used to seek (on SQL Server 2008 if the column is DATETIME date type then you can get a seek). -- Plamen Ratchev http://www.SQLStudio.com
From: Tom Cooper on 9 Mar 2010 11:34
I must second the comments that you should store datees in datetime, datetime2, or date columns. (datetime2 and date are onlyt available if you are using SQL 2008). I'm guessing that if you have 30710 in your column, you mean that to be March 7, 2010? That is for the date mm/dd/ccyy, your column contains 10,000*mm + 100*dd + yy If so, you could do something like With CalculateYesterday As (Select DateAdd(dd, -1, Current_TimeStamp) As Yesterday), FormatYesterday As (Select 10000 * Month(Yesterday) + 100 * Day(Yesterday) + (Year(YesterDay) % 100) As FormattedYesterday From CalculateYesterday) Select <column list> From <your table> Where DDate = (Select FormattedYesterday From FormatYesterday); Tom "jtibbs" <u58639(a)uwe> wrote in message news:a4bddfc978a5e(a)uwe... >I have a table in which the records contain a Date (DDate) that is stored >as > a Decimal value (IE. 30710). I need to SELECT from that table only the > records WHERE DDate is equal to yesterday (Current Date - 1) and I am > having > a heck of a time figuring out how to do this. Any help would be > appreciated! > Thanks! > |