Date to text conversion: MM/DD/YYYY; what am I doing wrong in this step?

[kdorian]

I am trying to convert a date in the format of m/d/YYYY to a text of MM/DD/YYYY, and I don't know what I'm doing wrong; I've tried several ways of converting a date to text, and while I do get A date in text format, it is not correct.

I tried breaking it down into several steps, and now it isn't making any sense at all.

I started with this:
Pay Day:

Code Select Expand

IF (character_length ('0' || cast(Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))=3)
THEN (cast(Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))
ELSE ('0' || cast(Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)) )

Pay Month:

Code Select Expand

IF (character_length ('0' || cast(Extract(MONTH, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))=3)
THEN (cast(Extract(MONTH, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))
ELSE ('0' || cast(Extract(MONTH, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)) )

Pay Date:

Code Select Expand

[Pay Month] || '/' || [Pay Day] || '/' || cast(Extract(YEAR, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(4))

which seemed to work when I wrote the program who-knows-when (I tested it a number of times), but now is not. A large percentage of the results seem to increase the month and day by one each, so 12/1/22 becomes 01/02/2022. Some of the dates, however, seem to come out of nowhere; someone who's last pay date was 12/1/22 can come out with a final payment date of 10/06/2022.

I tried breaking down the code into steps, and while the first part:
Pay Day extr:

Code Select Expand

Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Pay Date]))
works fine, the next part:

Code Select Expand

cast ([Pay Day extr], varchar(2))
only seems to be returning a value of 1 regardless of the original value - but that is not what's ending up in the final date.

I cannot figure out what is going wrong. Can someone help me please? I only just discovered there's an issue and I need this fixed by next week.

ETA: I should have included that the date needs to be in TEXT format. Just changing the way the dates are displayed won't help; it they are not text the file will be rejected.

[MFGF]

I am trying to convert a date in the format of m/d/YYYY to a text of MM/DD/YYYY, and I don't know what I'm doing wrong; I've tried several ways of converting a date to text, and while I do get A date in text format, it is not correct.

I tried breaking it down into several steps, and now it isn't making any sense at all.

I started with this:
Pay Day:

Code Select Expand

IF (character_length ('0' || cast(Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))=3)
THEN (cast(Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))
ELSE ('0' || cast(Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)) )

Pay Month:

Code Select Expand

IF (character_length ('0' || cast(Extract(MONTH, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))=3)
THEN (cast(Extract(MONTH, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)))
ELSE ('0' || cast(Extract(MONTH, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(2)) )

Pay Date:

Code Select Expand

[Pay Month] || '/' || [Pay Day] || '/' || cast(Extract(YEAR, maximum ([Business Layer].[Payroll History Summary].[Period End Date])), varchar(4))

which seemed to work when I wrote the program who-knows-when (I tested it a number of times), but now is not. A large percentage of the results seem to increase the month and day by one each, so 12/1/22 becomes 01/02/2022. Some of the dates, however, seem to come out of nowhere; someone who's last pay date was 12/1/22 can come out with a final payment date of 10/06/2022.

I tried breaking down the code into steps, and while the first part:
Pay Day extr:

Code Select Expand

Extract(DAY, maximum ([Business Layer].[Payroll History Summary].[Pay Date]))
works fine, the next part:

Code Select Expand

cast ([Pay Day extr], varchar(2))
only seems to be returning a value of 1 regardless of the original value - but that is not what's ending up in the final date.

I cannot figure out what is going wrong. Can someone help me please? I only just discovered there's an issue and I need this fixed by next week.

Hi,

Is there a reason you are using a complex conversion approach rather than just leaving the item as a date and using formatting to achieve the format you need? If not, you can just use the Data Format property for the item in your report, set the Format Type to be Date, then choose the Date Ordering, Display Years, Display Months and Display Days settings as desired?

Cheers!

MF.

[kdorian]

I'm sorry, I should have said that in my original post (now edited).

Yes, the date MUST be text. It will be part of an Excel file that is sent to the government, and the file will be rejected if the dates are not in text format. There are a number of dates used in different parts of the file, so entering them manually isn't practical.

[bus_pass_man]

Am I missing something?

Cast ( {whatever the column which contains the last pay date values}, varchar({whatever size you want so that everything fits}))

[dougp]

I'm seeing one problem here.  If you type a date "as text" in Excel, Excel will likely change it to a number (with a date format).  It seems "the government" requires you to do some work in Excel to make this work for them.  Regardless of what you do in Cognos, I think Excel will bork your results.  That makes this an Excel question, not a Cognos question.


To an optimist, the glass is half full.
To a pessimist, the glass is half empty.
To an engineer, the glass has a factor of safety of two.
To Excel, the glass is 44563 (January 2nd of this year).

[kdorian]

Am I missing something?

Cast ( {whatever the column which contains the last pay date values}, varchar({whatever size you want so that everything fits}))

That gives me a result of "1", and I have no idea why. I even tried splitting it up to get the max date in one field and then using that value in the cast, and got the same result (the max date was correct, I checked)

The one was from an incorrect aggregation. I fixed that, but the date is formatted as "YYYY-MM-DD" and I need "MM/DD/YYYY". I Goggled changing the date format in a cast and got back to the post that gave me the formula that I use in the original post, so it seems that won't work.

[kdorian]

I'm seeing one problem here.  If you type a date "as text" in Excel, Excel will likely change it to a number (with a date format).  It seems "the government" requires you to do some work in Excel to make this work for them.  Regardless of what you do in Cognos, I think Excel will bork your results.  That makes this an Excel question, not a Cognos question.

The field is formatted as text. If you type or paste a text value in it stays a text, but if you paste in something formatted as a date it becomes a date value.