GPSPOW
asked on
If then in where clause
I am trying to write a where clause if the month of today's date is = 1 then the date range is the beginning for the past year to the end of the past year, else it is the beginning of the current year to the end of last month.
Here is what I have now:
Thanks
Glen
Here is what I have now:
(dbo.OeOrders.OrderDateTime >= CAST(year(getdate())-1 as varchar(4))+'0101' AND dbo.OeOrders.OrderDateTime >= CAST(year(getdate()) as varchar(4))+'0101')
Thanks
Glen
ASKER
Unfortunately there is not a Calendar Table in the Sql Server.
I have always found that since the field data is formatted as datetime, the BETWEEN clause does not give me data after midnight on the end date.
Any suggestions?
Thanks
Glen
I have always found that since the field data is formatted as datetime, the BETWEEN clause does not give me data after midnight on the end date.
Any suggestions?
Thanks
Glen
Yeah, never use BETWEEN with date/datetime.
Don't use a calendar table unless you absolutely require it. Wasted I/O mostly, but also some logic errors.
(dbo.OeOrders.OrderDateTim e >= CASE WHEN month(getdate()) = 1
THEN CAST(year(getdate())-1 as varchar(4))+'0101'
ELSE dateadd(year, datediff(year, 0, getdate()), 0) END
AND
dbo.OeOrders.OrderDateTime < CASE WHEN month(getdate()) = 1
THEN CAST(year(getdate()) as varchar(4))+'0101'
ELSE dateadd(month, datediff(month, 0, getdate()), 0) END
Don't use a calendar table unless you absolutely require it. Wasted I/O mostly, but also some logic errors.
(dbo.OeOrders.OrderDateTim
THEN CAST(year(getdate())-1 as varchar(4))+'0101'
ELSE dateadd(year, datediff(year, 0, getdate()), 0) END
AND
dbo.OeOrders.OrderDateTime
THEN CAST(year(getdate()) as varchar(4))+'0101'
ELSE dateadd(month, datediff(month, 0, getdate()), 0) END
I've used Calendar tables with many clients with success, especially those that have custom business days that they need to track, or a fiscal calendar that doesn't have the same boundaries as a regular calendar.
I've seen people use calendar tables to count the number of days in a month -- huge waste of resources. Sometimes they get over-used for every calendar action, when they are actually only needed for a very small number of them.
I have a NonWorkDays table, not a full calendar table. They're shorter, and by its very nature can't be used when it's not needed :-).
I have a NonWorkDays table, not a full calendar table. They're shorter, and by its very nature can't be used when it's not needed :-).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Worked very well.
Glen
Worked very well.
Glen
Open in new window
btw if you had a Calendar Table in SQL Server, then you could join on the dates, do some math, and get all of the date boundaries you need, without having to use huge expressions.