First off let me say I am new to VB and my knowledge is rudimentary. Furthermore I am trying to pick up fixing someone else's code that I did not write. I apologize in advance for anything I do or say in this question that is frustrating or incomplete - it's not intentional and I'm happy to correct whatever faux pas I may be committing!
I have a question related to the code below:
case when CONVERT(date, (e3UsageFrom.DATE1)) = '1900-01-01'then ''
ELSE convert(varchar(10),(e3UsageFrom.DATE1),101) +''+CONVERT (CHAR(8),(e3UsageFrom.DATE1), 120)
END as UsageFrom,
The full code (which I've included below) is a stored procedure we that pulls Extender data onto a GP report writer report (among other things).
I am not receiving any errors, the code runs fine. However, when the Extender date fields print they print like this: DD/MM/YYYYYYY (for example 02/08/2016201) - in other words, the year data repeats.
I am guessing it has to do with the varchar data type and/or the number values, but I don't know what those indicate in the first place, so it's hard to know what to change. If someone could help me dissect that part of the code, I could test it and figure it out -
What do the numbers 10, 101 and 120 indicate? What is the significance of "convert" and "CHAR"? Why is the data type varchar instead of date? Would formatting these fields as a date solve my problem?
The full code for the stored procedure is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[dd_PA01201_SEL]
-- dd_PA01201_SEL 'sa'
@UserName varchar(30)
AS
select PAprojid, PABEndDate, pm.PAPROJNUMBER, e2.Extender_Record_ID, pm.CUSTNMBR,
isnull(e1Client.STRGA255,'') as Client,
isnull(e1Union.STRGA255,'') as UnionName,
pm.PAprjclsid, pm.PAprojname,
isnull(e3UsageFrom.DATE1,'') ,
case when CONVERT(date, (e3UsageFrom.DATE1)) = '1900-01-01'then ''
ELSE convert(varchar(10),(e3UsageFrom.DATE1),101) +''+CONVERT (CHAR(8),(e3UsageFrom.DATE1), 120)
END as UsageFrom,
isnull(e3UsageTo.DATE1,'') ,
case when CONVERT(date, (e3UsageTo.DATE1)) = '1900-01-01'then ''
ELSE convert(varchar(10),(e3UsageTo.DATE1),101) +''+CONVERT (CHAR(8),(e3UsageTo.DATE1), 120)
END as UsageTo,
isnull(e3HoldFrom.DATE1,'') ,
case when CONVERT(date, (e3HoldFrom.DATE1)) = '1900-01-01'then ''
ELSE convert(varchar(10),(e3HoldFrom.DATE1),101) +''+CONVERT (CHAR(8),(e3HoldFrom.DATE1), 120)
END as HoldFrom,
isnull(e3HoldTo.DATE1,''),
case when CONVERT(date, (e3HoldTo.DATE1)) = '1900-01-01'then ''
ELSE convert(varchar(10),(e3HoldTo.DATE1),101) +''+CONVERT (CHAR(8),(e3HoldTo.DATE1), 120)
END as HoldTo,
isnull(e1ProductionCo.STRGA255, '') as ProductionCo,
isnull(e2Signatory.STRGA255,'') as Signatory,
isnull(ub.DateRange, '') as DateRange,
isnull(ub.PACOSTCATNME,'') as PACOSTCATNME,
isnull(ub.EMPLOYID,'') as EMPLOYID,
ISNULL(ub.d1, '') as d1,
ISNULL(ub.d2, '') as d2,
100 * isnull(ub.PAQtyQ,0) as PAQtyQ
from UPR40200EXT ux
join PA01201 pm on pm.PAPROJNUMBER = ux.projectnumber --Project Master
left join EXT01100 e0 on e0.Extender_Window_ID = 'PROJECT_DETAILS' and e0.Extender_Key_Values_1 = pm.PAprojid --Extender Header
left join EXT01100 e2 on e2.Extender_Window_ID = 'PROJ_SIGNATORY' and e2.Extender_Key_Values_1 = pm.CUSTNMBR --Extender Header
left join EXT01101 e1Client on e1Client.Extender_Record_ID = e0.Extender_Record_ID and e1Client.Field_ID = 1290 --Extender client line
left join EXT01101 e1Union on e1Union.Extender_Record_ID = e0.Extender_Record_ID and e1Union.Field_ID = 1299 --Extender union line
left join EXT01102 e3UsageFrom on e3UsageFrom.Extender_Record_ID = e0.Extender_Record_ID and e3UsageFrom.Field_ID = 1269 --Extender usage from line
left join EXT01102 e3UsageTo on e3UsageTo.Extender_Record_ID = e0.Extender_Record_ID and e3UsageTo.Field_ID = 1270 --Extender usage to line
left join EXT01102 e3HoldFrom on e3HoldFrom.Extender_Record_ID = e0.Extender_Record_ID and e3HoldFrom.Field_ID = 1272 --Extender hold from line
left join EXT01102 e3HoldTo on e3HoldTo.Extender_Record_ID = e0.Extender_Record_ID and e3HoldTo.Field_ID = 1274 --Extender hold to line
left join EXT01101 e1ProductionCo on e1ProductionCo.Extender_Record_ID = e0.Extender_Record_ID and e1ProductionCo.Field_ID = 1290 --Extender ProductionCo line
left join EXT01101 e2Signatory on e2Signatory.Extender_Record_ID = e2.Extender_Record_ID and e2Signatory.Field_ID = 1296 --Extender signatory line
--THIS CAUSES THE STORED PROC TO RETURN ONE LINE FOR EACH PAYCODE
left join (
SELECT
tsd.PACOSTCATID
,cc.PACOSTCATNME
,tsh.EMPLOYID
,TSD.PAQtyQ
,convert(varchar(10),MAX(padt),101) + ' - ' + convert(varchar(10),MAX(PAexptdate),101) DateRange
,MAX(tsd.padt)d1
,case when CONVERT(date, MAX(tsd.padt)) = '1900-01-01'then ''
ELSE convert(varchar(10),MAX(tsd.PADT),101) +''+CONVERT (CHAR(8),MAX(tsd.PADT), 120)
END d3
,MAX(tsd.PAexptdate)d2
,case when CONVERT (date, MAX(tsd.PAexptdate)) = '1900-01-01' then ''
ELSE convert(varchar(10), MAX(tsd.PAexptdate),101)+''+CONVERT (CHAR(8),MAX(tsd.paexptdate), 120)
END d4
from UPR10301 ub
join PA30100 tsh on tsh.BACHNUMB = ub.BACHNUMB
join PA30101 tsd on tsd.PATSNO = tsh.PATSNO
join PA01001 cc on cc.PACOSTCATID = tsd.PACOSTCATID
where ub.MKDBYUSR = @UserName
and ub.UPRBCHMK = 1
group by ub.BACHNUMB,tsd.PACOSTCATID,cc.PACOSTCATNME, tsh.EMPLOYID,TSD.PAQtyQ,PADT,PAexptdate
) ub on 1=1
--select * from EXT01100 where Extender_Window_ID = 'PROJ_SIGNATORY ' and Extender_Key_Values_1 = 'anot0001'
--select * from EXT01101 where Extender_Record_ID = 5194
--select * from pa01201
-- update UPR40200EXT set ProjectNumber = 'ALLI2C40094 '
GO