WB19 - Create SQL Data Source on DOB by a particular month

Startbeitrag von Joel am 30.07.2015 06:28

I need to create a Data Source where the DOB month = a selected month (in alpha)...

try as I might, I can't seem to get this to work:

grsSql is string = [
SELECT *
FROM gpsdContact
WHERE bActiveMembershipFlag = 1
AND
dDOB > '19000101'
AND
TO_CHAR(dDOB,MONTH) = '%1'
]

(Instead of TO-CHAR, I've also tried WL.DateToString(dDOB,'MMMM') = '%1')

grsSql = StringBuild(grsSql,"June")
HExecuteSQLQuery(grsdDataSource,grsSql)

(The dDOB > '19000101' is to handle case where there is no date in the field)

Can anyone tell me where I'm going wrong?

Antworten:

Hi Joel,

You are unnecessarily complicating the query by feeding the query parameter as a human readable month instead of directly using the value as stored in the database. (YYYYMMDD)

SELECT *
FROM gpsdContact
WHERE
SUBSTRING(gpsdContact.dDOB ,5,2) = '08'

will return all records holding the month of august.
Just use a list or combo giving a returned value of '01'-'12' to let the user choose a month.

Regards,
Piet

von Piet van Zanten - am 31.07.2015 11:17
Thanks Piet -

Yep, I tend to do that - make things more complicated - that's inexperience for you! lol

von Joel - am 31.07.2015 18:08
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.