Hello,
I'm trying to change the date format in a query element to make it user friendly for WordFly and I can't seem to get it work. Looking at the documentation, I should be able to use the CONVERT function in the system table to do so, but I'm getting an error. Alternatively, I tried setting up a new view and did the conversion there and that seems to work, but I can't get the elements in the new view to pull into my output set. I've granted SELECT permissions to ImpUsers and Tessitura_App and WordFly in SSMS.
And also related -- even when I have the output set working (with the dates in the wrong format, but the data pulling in from another view), WordFly can't seem to find those fields -- can it not use custom output elements? They aren't showing up as fields to be mapped.
Anne
Anne,
What error do you get when you try to save the system table with the CONVERT function? Also, what format are you hoping to get output? (Put out?) (Out of your output?)
John A. Moskal II
Hi John,
We're trying to set up email renewal notices so I need to include the expiration date -- either current or past (hence the custom view). Ideally, it would be "August 29, 2023," but I can live with "Aug 29, 2023" (which seems to be the closest I've gotten).
Yeah, if you want a full month name, you are going to have to do some concatenated DATEPART() + OTHERSTUFF() sort of thing to get you there. I am not aware of any CAST() or CONVERT() out of the box that spits out full month names.
As for the rest, Gawain Lavers speaks the truth. It can be odd what SHOULD work that, for whatever reason, does not.
Yeah, I'm not seeing a full month name option here:
learn.microsoft.com/.../cast-and-convert-transact-sql
That's where I was looking too. Like I said, I can live with option 107 which gave me "Aug 29, 2023."