DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In
laura@matthewsconsulting.com 2/8/2016 8:44:58 PM

Dates are repeating the year when using VB to pull Extender fields

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

Version: All
Section: Dynamics GP, VBA