SAP Business One Birthday List! (without the year)

A quick and easy query to give you a list of the birthdays from the Employee Master Data application in SBO.

Copy and paste the lines below into the Query Generator tool.

/*———————–*/
/* Displays a list of all the employees in SBO with their birthdays (without the year) */

SELECT
lastname as ‘Last Name’
,firstname as ‘First Name’

,Case When birthdate is Null then ‘Not Entered’ Else
CASE len(CONVERT(varchar(2), datepart(mm, birthdate)))
WHEN 1 THEN ‘0’ + CONVERT(varchar(1), datepart(mm, birthdate))
ELSE CONVERT(varchar(2), datepart(mm, birthdate))
END
+ ‘/’ +
CASE len(CONVERT(varchar(2), datepart(dd, birthdate)))
WHEN 1 THEN ‘0’ + CONVERT(varchar(1), datepart(dd, birthdate))
ELSE CONVERT(varchar(2), datepart(dd, birthdate))
END

END AS Birthday

FROM OHEM
Order by birthday, lastname, firstname

/*———————–*/

If you wanted to access the Employee Master from this query you could add:

empid,

after the Select

and

FOR BROWSE

on the line following the Order By

If you would like to make this query into an alert, you could do all of the above and add the line

WHERE birthdate is not null

between the FROM OHEM and the Order By.

That would look like:

/* Displays a list of all the employees in SBO with their birthdays */
/*(without the year) */

SELECT
empid as ‘ID’
,lastname as ‘Last Name’
,firstname as ‘First Name’

,Case When birthdate is Null then ‘Not Entered’ Else
CASE len(CONVERT(varchar(2), datepart(mm, birthdate)))
WHEN 1 THEN ‘0’ + CONVERT(varchar(1), datepart(mm, birthdate))
ELSE CONVERT(varchar(2), datepart(mm, birthdate))
END
+ ‘/’ +
CASE len(CONVERT(varchar(2), datepart(dd, birthdate)))
WHEN 1 THEN ‘0’ + CONVERT(varchar(1), datepart(dd, birthdate))
ELSE CONVERT(varchar(2), datepart(dd, birthdate))
END
END AS Birthday

FROM OHEM
WHERE birthdate is not null
Order by birthday, lastname, firstname
FOR BROWSE

 

Thanks to Ed Monk of SBONotes.com

2019-01-16T11:44:19+00:00 February 11th, 2009|SAP Business One|Comments Off on SAP Business One Birthday List! (without the year)