Hi there!
My organization would like to start sending automated birthday emails using WordFly. We currently store birthdays as an attribute. In order to integrate with WordFly, I need to create a dynamic list in Tessitura using the birthday attribute criteria. I'm in the testing phase right now, and when I configure the data to equal my own birthday, I receive the following error message and I'm not sure why.
"List generation has failed.: Error 242, Severity 16, State 3, Procedure n/a, Line 1, The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
Later I'll need to take this list one step further and configure the criteria to equal a specified month and date, but any year. Has anyone created a list like this before? How did you do it?
Is your birthday attribute set up as a datetime field? I have often seen them set up as a string, and that causes a lot of trouble when trying to do date filtering.
As Maery described, varchar birthdates will be troublesome for SQL to work with. If your data is anything like my old conversion data was re: birthdates, e.g. "Jan 10", or "1945/01/15", or a variety of other formats, all living side by side, you may have a cleanup task ahead of you.
Hi Marissa,
Here's some code that you can use in List Manager. You'll want to change the keyword_no = 1 to whatever the id is for your Birthdate keyword. You can edit then pick the month and date you're wanting to use in the last two criteria. I added a couple of other fields that you might want to use as a cross check in SQL. All you'll need to do is remove the -- that comment them out for List Manager. Hope this helps! -Sara
SELECT DISTINCT customer_no --, key_value AS Birthdate, DATEPART(month, key_value) AS Birth_Month, DATEPART(Day, key_value) AS Birth_DayFROM TX_CUST_KEYWORDWHERE keyword_no = 1 AND DATEPART(month, key_value) = 11 AND DATEPART(Day, key_value) = 2
Where can you check for that? in System Tables or ? I think this is part of our problem. I try to get just January birthdays, for example, and I get everyones.
Would you be willing to share a screenshot of your List Manager criteria screen? I'm having a hard time building this list. Thanks so much!
There's a system table called T_KEYWORD that contains all attributes and list builder criteria - you can find the Birthday attribute there and look at the 'Data Type'.