Recently, I was working on a project where I had to write lots of reporting queries based on the date. I wrote some SQL queries where I used lots of date functions. I thought of writing a blog for this.
1) I had a table where my columns had data type date and when I used to select the column and output was like:
2011-10-11
2011-10-11
2011-10-11
2011-10-11
2011-10-12
But in reports this was not user friendly so, I twisted the query little bit:
select convert(varchar(11),date_col ,100) from and the output was as required:
Oct 11 2011
Oct 11 2011
Oct 11 2011
Oct 11 2011
Oct 12 2011
Oct 3 2011
2) Similarly, there was a requirement when I had to do group by – year. I tried to use substring function but got an error while execution:
Msg 8116, Level 16, State 1, Line 1
Argument data type date is invalid for argument 1 of substring function.
Which makes sense. and I had to find some other way. Here is what I did:
select YEAR() as [Year] , from dbo.T_activites_cost_execution
group by YEAR(),
Please note: – Date_col is the column with the datatype date
Output was like:
Year ———– —————————————
2010 16398036
2011 22015269
3) Now, after the yearly report I had to create one quarterly. The query which I created was:
select ‘Q’+Datename(qq,) AS Qtr , Col
from
Output was like :
Qtr Actual_Cost
——————————- —————————————
Q2 40698
Q2 4500
Q3 4334
There are basically 2 functions to get the specific information from date column
1. DATEPART –>Returns Numeric
2. DATENAME –>Returns Varchar (Further, substring function can be used)
Even we can get second, milisecond part from the date column using any of the above functions.
HTH!