Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having trouble with data that I'm pulling
So generally my dates are broken down to Century and Date which includes YY/MM/DD (i.e: Cen =20 + Date = 180201)
However some single digit years i.e '050302' is missing the '0' as the first digit so it shows up as '50302'
When I try to concatenate the Century and Date for those ones I get something like this 2050/4/24 but needs to look like 2005/04/24
How do I add a ZERO so the date shows up properly?
VLUPD = Date
if ( left(VLUPD,2) >=10,'Y','N') as TEST_FLAG,
left(VLUPD,2) as TEST_YY,
if(VLUPD >=10,MakeDate(Left(Text(VLUPC&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2)),
MakeDate(Left(Text(VLUPC&'0'&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2))) as [Last UPDATE Date],
Please let me know of any solution thanks so much
-Erk
I think you can try
if(Len(VLUPD)<6,0&VLUPD,VLUPD) AS VLUPD1
How are your dates in 2000 showing up? Do you have dates from then?
The expression might have to be tweaked a little to deal with those.
Thank you for your reply Jahanzeb
if(Len(VLUPD)<6,0&VLUPD,VLUPD) AS VLUPD1,
left(VLUPD,2) as TEST_YY,
if(VLUPD <6,MakeDate(Left(Text(VLUPC&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2)),
MakeDate(Left(Text(VLUPC&'0'&VLUPD),4), Mid(Text(VLUPD),3,2),Right(Text(VLUPD),2))) as [Last UPDATE Date],
So something like that?
Try Num(VLUPD, '000000') as [Last UPDATE Date]