Lead Time Conversion

sbolton1855

I am writing a SSRS report that exports fields from the Item table. One of the fields i need is Safety Lead Time. Is there a way to convert this field in a way SSRS understands? thanks

Savatage

What exactly is it not understanding?
What kind of result would you like to see?

kine

Format o DateFormula datatype on SQL was already discussed few years back.Just search the forum for DateFormula and SQL.

sbolton1855

The 'lead time calculation' in the 'Item' table is a varchar(32), this is not a normal date. It is my understanding that he NAV system interprets this string to an output such as 3 day, 1 week etc calculation.
I dont think DateFormula and SQL will get me where i am going, since this field is not a datetime datatype, it is a varchar(32).

sbolton1855

ah you were right DateFormula :whistle:
replace(replace([Lead Time Calculation],CHAR(1),'C'),CHAR(2),'D') this seems to get me about half way there, some of the fields are still displaying wrong.....

sbolton1855

this seems to be as far as i can go with the data in my system

[Lead Time Calculation], 
REPLACE(REPLACE(REPLACE(REPLACE([Lead Time Calculation], 
CHAR(1), '?ContactAdmin'), CHAR(2), 'D'), CHAR(3), '?ContactAdmin'), CHAR(4), 'W')

kine

C = 0x01 *current
D = 0x03
W = 0x04
M = 0x05
Y = 0x07

quoted from viewtopic.php?f=23&t=20075&p=94085

darko

I got the whole thing here:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([dbo].[CompanyName$Item].[Lead Time Calculation],
CHAR(1), 'C'), CHAR(2), 'D'), CHAR(3), 'WD'), CHAR(4), 'W'), CHAR(5), 'M'), CHAR(6), 'Q'), CHAR(7), 'Y')
--C=current, D=day, WD=weekday, W=week, M=month, Q=quarter, Y=year)
Hope it helps.