July 11, 2013 at 8:14 am
Hello,
I would like to create a SQL query that will make a decision based on date. For example, if the date is Jun 30,2013 or greater, then show 2013, else 2012.
IF myDate>6/30/2013 THEN '2013' ELSE '2012' as getSeason FROM myTable
I know this is not the write syntax, but is it possible to do something this way?
July 11, 2013 at 8:22 am
rayh 98086 (7/11/2013)
Hello,I would like to create a SQL query that will make a decision based on date. For example, if the date is Jun 30,2013 or greater, then show 2013, else 2012.
IF myDate>6/30/2013 THEN '2013' ELSE '2012' as getSeason FROM myTable
I know this is not the write syntax, but is it possible to do something this way?
Do you mean the current date? In other words if you run the query before June 30th it will show the previous year data but if you run the query after June 30th it will show the current year data? A little bit more detail is required here to provide an accurate answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 8:22 am
rayh 98086 (7/11/2013)
Hello,I would like to create a SQL query that will make a decision based on date. For example, if the date is Jun 30,2013 or greater, then show 2013, else 2012.
IF myDate>6/30/2013 THEN '2013' ELSE '2012' as getSeason FROM myTable
I know this is not the write syntax, but is it possible to do something this way?
the CASE statement is used in sQL to do that; but in your case, you might want to use the YEAR function for this specific issue;
so it would look something like this:
SELECT
OtherColumns,
CASE
WHEN myDate > '6/30/2013'
THEN '2013'
ELSE '2012'
END as getSeason
FROM myTable
--or
SELECT
OtherColumns,
YEAR(myDate) AS getSeason
FROM myTable
Lowell
July 11, 2013 at 8:36 am
Thank you both!
Sean, I should have been more clear, I have a date field that I am calling myDate and want to check against the field (not using current date/GetDate()). I always appreciate you help!
Lowell, this works great!
The next step I would be looking to do is be able to change the then and else values based on the date field year.
If the date field year is 2014 and greater it wouldnt make sense to have the THEN be 2013 and ELSE be 2013
For example:
SELECT
OtherColumns,
CASE
WHEN myDate > '6/30/2015'
THEN '2013'
ELSE '2012'
END as getSeason
FROM myTable
Can I make the THEN ELSE values vary based on the myDate field's year?
July 11, 2013 at 8:46 am
ok ray, this looks like a business "fiscal year" question, right?
ie my biz year goes from july1 to jun30 of the next year?
substitue MyDate for GETDATE() in this query, and it will do what you ask for, i think;
see how i'm setting my biz date based on # days from the beginning of the year to calculate it....Feb 2014 would have a biz year of 2013 in my example
--# days since the first of the year:
select
DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),GETDATE()) As Dayz, --ie 191 for 07/11/2013
CASE
WHEN DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),GETDATE()) > 180
THEN YEAR(GETDATE())
ELSE YEAR(GETDATE()) -1
END As BizYear
Lowell
July 11, 2013 at 9:00 am
Thanks again!!!!!!!:-)
This forum is awesome!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply