SQLTeam.com | Weblogs | Forums

How do I format dateCreated db column to produce just Year in the stored proc below?


#1
Greetings experts,

I have a dynamic query that I integrated into a stored procedure.

Here is the query:

    CREATE PROCEDURE dbo.uspGetPivotedData
AS
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(d.dateCreated) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from 
            (
                select employeeName
                    , empTitle
                    ,email
                    , dateCreated,sourceincome
                from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
                inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
          ) x
            pivot 
            (
                max(sourceincome)
                for dateCreated in (' + @cols + ')
            ) p '
            EXECUTE sp_executesql @query

The query works great. The only thing that I would need your help in formatting is how to display dateCreated field as Year.

For instance, instead of April 2019, we would like to just display 2019.

Is this possible?

Thanks a lot in advance


#2

What datatype is dateCreated column?

If it's char or varchar, does it contain "{monthname} {year}" for ALL rows? Or are there exceptions?


#3

Thank you for your response sir.

It is datetime datatype.


#4

year(dateCreated) ?


#5

Where do I try this on the code?

I tried that already and it gave me an error.


#6

If the code gave you an error, then what was the error? What syntax did you use? This works

drop table if exists #t
create table #t (DateCreated datetime)

insert into #t values 
('1/1/2016'),
('1/1/2017'),
('1/1/2018'),
('1/1/2019'),
('1/1/2020')
select datepart(year, DateCreated), 
       Year(DateCreated)
  from #t

#7

hi

will this work ???

your SQL
CREATE PROCEDURE dbo.Uspgetpivoteddata 
AS 
    DECLARE @cols  AS NVARCHAR(max), 
            @query AS NVARCHAR(max); 

    SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(year(d.datecreated)) 
                       FROM   datedetails d 
                       FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1 
                , '' 
                ) 
    SET @query = 'SELECT employeeName,empTitle,email ' 
                 + @cols 
                 + ' from              (                 select employeeName                     , empTitle                     ,email                     , dateCreated,sourceincome                 from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID                 inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID           ) x             pivot              (                 max(sourceincome)                 for year(dateCreated) in (' 
                 + @cols + ')             ) p ' 

    EXECUTE Sp_executesql 
      @query

i just added year in these 2 places
does it work ???
please let me know :slight_smile: :slight_smile:

Quotename(year(d.datecreated))
for year(dateCreated) in ('


#8

Greetings experts,

Thanks for your responses.

mike01, what error?

I am getting:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.

This error occurs when I use Y(d.dateCreated) or harishgg1's solution.

When I use mike01's solution, I get no errors but I get no results.


#9

I have no way of testing this, as I don't have your data to test on, but I think this just might work for you:

create procedure dbo.uspGetPivotedData
as

declare @cols  as nvarchar(max)
       ,@vals  as nvarchar(max)
       ,@query as nvarchar(max)
;

set @cols=stuff((select distinct
                        ','
                       +'['+datepart(year,dateCreated)+']'
                   from DateDetails
                    for xml path('')
                           ,type
                ).value('.', 'nvarchar(max)')
                ,1
                ,1
                ,''
               )
;

set @vals=replace(replace(@cols,'[',''),']','');

set @query='select employeeName
                  ,empTitle
                  ,email
           '
          +@cols
          + ' from (select employeeName
                          ,empTitle
                          ,email
                          ,year(dateCreated) as dateCreated
                          ,sourceincome
                      from SourceDetails
                           inner join Employees
                                   on Employees.EmployeeID=SourceDetails.employeeID
                           inner join DateDetails
                                   on DateDetails.employeeID=SourceDetails.employeeID
                   ) x
             pivot (max(sourceincome)
                    for dateCreated in ('+@vals+')
                   ) p
            '
;

execute sp_executesql @query;

#10

I am not sure how this works as written - this query:

Would return a column for every date - as [Jan 11 2019 12:53PM] - not MMM YYYY as in your example unless d.dateCreated is actually stored as a varchar in that format.

If you want this to only display the year - then you need to do a couple of things. First, you need to change this: QUOTNAME(d.dateCreated) to: QUOTENAME(year(d.dateCreated)) - this will generate a list of all possible years from DateDetails table.

Next - you need to modify the query to return the year(dateCreated)...

Then - you need to modify the pivot to use the new column...

    CREATE PROCEDURE dbo.uspGetPivotedData
AS
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(d.dateCreated)) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from 
            (
                select employeeName
                    , empTitle
                    ,email
                    , YearDate = year(dateCreated)
                    ,sourceincome
                from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
                inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
          ) x
            pivot 
            (
                max(sourceincome)
                for YearDate in (' + @cols + ')
            ) p '
            EXECUTE sp_executesql @query

#11

Thank you all very much for the assistance.
Much appreciated.

@bitsmed, your code is giving the following error:

Conversion failed when converting the varchar value ',[' to data type int.

@jeffw8713, your solution is giving the following error:

The incorrect value "[2018]" is supplied in the PIVOT operator.

However, through trial and error, I am able to get the following to work:

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy'))
...
...

Hopefully, no hidden issues with my solution.

Thank you all again for your attention to my thread.


#12

2 questions,

what does @cols hold? Also, what does @query hold?


#13

this is just so that others can work with sample data, use it in a a dev database that has lots of tables since I am using sys.tables

use master
go

create table DateDetails(employeeID int, dateCreated datetime);
insert into DateDetails
select distinct top 12 object_id, create_Date 
--
from sys.tables 

create table SourceDetails(employeeID int)

insert into SourceDetails
select employeeID From DateDetails

create table Employees(employeeID int , employeeName varchar(50), 
empTitle varchar(50), email varchar(50), sourceincome varchar(50))

insert into Employees(employeeID,employeeName, empTitle, email, sourceincome)
select distinct employeeID, t.name, 'title ' + t.name,
 t.name + '@nodata.com', t.name + ' benjamins'
  From DateDetails dd
  join sys.tables  t on dd.employeeID = t.object_id

 --select * from Employees
 --select * from DateDetails
 --select * from SourceDetails


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(YEAR(d.dateCreated)) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols

set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from 
            (
                select employeeName
                    , empTitle
                    ,email
                    , YEAR(dateCreated) as dateCreated
					,sourceincome
                from SourceDetails 
inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
          ) x
            pivot 
            (
                max(sourceincome)
                for dateCreated in (' + @cols + ')
            ) p '
            EXECUTE sp_executesql @query

drop table DateDetails
drop table SourceDetails
drop table Employees


#14

Thanks a lot yosiasz.

This works perfectly.

All of you guys here are very tenacious.

Thank you so very much.