Wish list
If you like this blog and want to say thanks in some way, here is my wish-list on Amazon. It'll be very cool to get a gift from my subscribers. Just don't forget to put your name on it -)Tags
Tag Archives: sql
List users per role
Admin Console of HR.Net allows only to list users by their primary role. And secondary roles are in a shade. To display all users that belong to a particular role (primary or secondary) you can use this bit of sql: … Continue reading
Function in computed field in temp table
It is not easy to set up function on computed field in temp table. I spent the whole day trying to resolve this… and here is the answer: use hrnetdev declare @functions nvarchar(4000) set @functions = ‘ use [tempdb] IF … Continue reading
Table definitions
For those of you wanting to know where HRNET stores meta information about tables and table-fields: look into oc_fileddefs for field definitions – type of data, validation, etc. For field captions and descriptions go to oc_filedlanguages.
PHP Error: unable to get Memo fields from HRNET
When I was doing select statement from HRNET database, one of the problems was that I got error looking like this: warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using … Continue reading
Find End of a Day for Given Date
Determines the last second for the day for the given date CREATE FUNCTION [dbo].[EndOfDay] ( @DATE DATETIME ) RETURNS DATETIME AS BEGIN RETURN DATEADD(s,-1,DATEADD(d, DATEDIFF(d,0,@DATE)+1,0)) END
Find Start of a Year for Given Date
Finds out a start of the year for the given date. CREATE FUNCTION [dbo].[StartOfYear] ( @DATE DATETIME ) RETURNS DATETIME AS BEGIN RETURN DATEADD(yy, DATEDIFF(yy,0,@DATE),0) END
Find Start of a Month for Given Date
Finds Start of the month with given date CREATE FUNCTION [dbo].[StartOfMonth] ( @date datetime ) RETURNS datetime AS BEGIN return DATEADD(M,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0)) END
End of Month for Given Date
Finds end of the month with given date Create FUNCTION [dbo].[EndOfMonth] ( @date datetime ) RETURNS datetime AS BEGIN return DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0)) END
End of a Year for Given Date
Function to find latest second of the year for the given date Create FUNCTION [dbo].[EndOfYear] ( @DATE DATETIME ) RETURNS DATETIME AS BEGIN RETURN DATEADD(s, -1, DATEADD(yy, DATEDIFF(yy,0,@DATE)+1,0) ) END
Picklist search
Sometimes it is handy to have a deeper look on the HR.Net Picklists. Picklists are set values of text data. Opposing to plain text fields, picklists can have only set number of values and can not be easily changed by … Continue reading