SQL Trick KB: Algorithm - First Date of Financial Year

I was trying to implement a stored procedure with some function working based on the first date of the financial year for any given date. In Australia, our financial year start on the 1st of July. So, for example, if today is '2015-06-30', then it should return the '2014-07-01' as the first date of FY15, while if the date '2015-07-02' would return the next financial year FY16 with the start date as '2015-07-01'.

Sounds easy, huh? I was working on some CASE...WHEN statement, until I found this piece of brilliant, clean-cut idea, and thought that this is just so simple. It is from damien-the-unbeliever's answer at the Most efficient way to calculate the first day of the current Financial Year? at stackoverflow.com.

SQL Trick KB: Function - How to check the datatype for a field or variable with T-SQL

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Warning: This is not necessary useful - until you need it or in my case, to be honest, when you are getting too bored.

One day, I try to verify what datatype of a field that is used by a stored proceduce. Clicking through the GUI is too hard and un-cool. So, Google tells me how to achieve it. There is a built in function called SQL_VARIANT_PROPERTY and this is the reference to it: MSDN - SQL_VARIANT_PROPERTY (Transact-SQL)

SQL Trick KB: Algorithm - Convert seconds in into Hours:Minutes

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Here is just a knowledge capture so I that I don't need to think about it from scratch again.

A BI developer buddy comes to me for help with converting a data field, which is in seconds, into something more user-friendly, Hours:Minutes. Thought it would be supper easy task but when I claw further toward the goal, I find it is a bit of work than just maths.

Here is the code:

SQL Trick KB: Algorithm - Last date of the month

This post was published at my beloved old blog "On the quest of a DBA's adventure......"

".... Hang on. How hard could it be? "

In a quiet afternoon a while ago, I was enjoying the challenge from a T-SQL quiz question. It asked how to get the last date of the month for a given date, say for reporting purpose. I thought: ".... Hang on. How hard could it be?". It successfully occupied me mentally for an hour.

The main deal is that the last day of a month is not necessary in a regular pattern. It could be 30, 31, ..... 28, or even 29. It all depends on which month and which year you are talking about. I was thinking to take the given date and disassemble it, took the month and make up the first day then subtract 1 day... too complex,really.

Inspired by Pinal Dave's blog post SQL SERVER – Query to Find First and Last Day of Current Month . Here is the code:

It returned a list of the error in different languages. If you are lucky, you now know what they mean. OK, it is not "world-savingly" great, but it may be a little step forward to the solution at least.

Here's a different approach to get last day of the month for a given date