4 Replies Latest reply: Apr 14, 2016 4:04 PM by Mark Graham RSS

    0 value in date

    Mark Graham

      Hi All,

       

      I have a date filed which is in the format of YYYYMMDD (Which has 0 value - on a purpose)

       

      I'm converting it to MM/DD/YYYY by the statement:

       

      MakeDate(Left(Test,4),Mid(Test,5,2),Right(Test,2))

       

      But when i want to retain 0 value too, it i snot fetching it.

      Can someone please look in to the application attached??

        • Re: 0 value in date
          Sunny Talwar

          I don't seem to understand the issue. 0 is showing up on all the three fields here, isn't it?

           

          Capture.PNG

            • Re: 0 value in date
              Mark Graham

              It is missing the format in the 'Test Final'.

               

              When format shows up, 0 will not.

               

              In 'Test Final' i'm looking for o and also dates with 'MM/DD/YYYY' format

                • Re: 0 value in date
                  Sunny Talwar

                  Try this:

                   

                  Table1:

                  LOAD *,

                    If(IsNull([Test Temp]),0,[Test Temp]) as [Test Final];

                  LOAD *,

                    If([Test]='0', 0, Date(MakeDate(Left(Test,4),Mid(Test,5,2),Right(Test,2)))) as [Test Temp];

                  LOAD * Inline

                  [

                  Test

                  0

                  20160215

                  20160317

                  20160318

                  20160319

                  20160322

                  20160323

                  20160324

                  20160326

                  20160327

                  20160329

                  20160330

                  20160331

                  20160402

                  20160403

                  20160404

                  20160405

                  20160406

                  20160407

                  20160408

                  20160409

                  20160410

                  20160411

                  20160412

                  20160413

                  20160414

                  20160415

                  20160416

                  20160417

                  20160418

                  20160419

                  20160420

                  20160425

                  20160426

                  20160429

                  20160502

                  20170202

                  20170217

                  20300101

                  20300303

                  20300707

                  20300909

                  20500505

                  30300101

                  30300330

                  30300707

                  30300909

                  50500505

                  60160102

                  60160105

                  60160106

                  60160113

                  60160212

                  60160215

                  60160217

                  60160409

                  ];