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!