Zoho People: Reject an Application for Leave in Deluge based on Criteriahttps://joellipman.com/articles/crm/zoho/zoho-people/zoho-people-reject-an-application-for-leave-in-deluge.html

10 December 2020 holiday clashes) Prevent an employee for applying for leave if over 80% of the department have already booked the requested dates off. In other words, if there are 10 people in a department/team, and 8 people have booked tomorrow off, prevent a 9th team...

• Type: Article
• Author: Joel Lipman
• Category: Zoho People
• Language: *

Set default parameter dates to start and end of monthhttps://joellipman.com/component/content/article/set-default-parameter-dates-to-start-and-end-of-month.html?catid=75&Itemid=165

05 April 2011 SSRS has always been a date range rather than just the one date. The user must be allowed to select all results between two dates instead of just specifying the one day. If the second parameter (ToDate) is the same as the first (FromDate), then the...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

SQL Calendar in Business Intelligence Development Studiohttps://joellipman.com/articles/else/database/t-sql/sql-calendar-in-business-intelligence-development-studio.html

14 January 2011 producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database. I'm calling this project "Reinventing the Wheel"...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Difference between two dates - the midnight hourhttps://joellipman.com/articles/cms/website-development/difference-between-two-dates-the-midnight-hour.html

06 April 2011 is not changing the date at the stroke of midnight. Take the last row as an example, the equation that's happening is: $thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession'])); $this_time_from1 = date("H:i",...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• Language: *

Zoho Deluge: Calculate start and end of Daylight Savings Timehttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-calculate-start-and-end-of-daylight-savings-time.html

07 December 2021 An article to document how I calculated the start and end dates of when Daylight Savings Time is in effect. Why? Admittedly, most of Zoho apps have this built-in but here's some snippets of code in case you need them. How? I will use this to amend and...

• Type: Article
• Author: Joel Lipman
• Category: Zoho Deluge
• Language: *

Zoho Deluge: Today, Tomorrow, Day After but Skip Sundayhttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-today,-tomorrow,-day-after-but-skip-sunday.html

02 November 2021 + v_Day3_Ordinal + " " + v_NextDay.toString("EEEE") + " " + v_NextDay.toString("MMMM") + " "; Generate a list of working dates given a number of days So here's another scenario where you have a start date and you are asked to list the working days, 7...

• Type: Article
• Author: Joel Lipman
• Category: Zoho Deluge
• Language: *

SSIS Convert a string into a datehttps://joellipman.com/component/content/article/ssis-convert-a-string-into-a-date.html?catid=74&Itemid=165

14 March 2013 the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8). Why? It took me a long time to figure this...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

Split a row into multiple rows based on a column valuehttps://joellipman.com/articles/else/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 1997-11-17 002 1.00 2000-02-18 002 1.00 2000-02-19 002 1.00 2000-02-20 003 1.00 1999-02-25 003 1.00 1999-02-26 -- note the dates increment and do not account for days off (eg. Saturday / Sunday) How? Adapted from a forum topic: SQLTeam Forums First of...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Inserting incremental weeks in MySQLhttps://joellipman.com/articles/else/database/mysql/inserting-incremental-weeks-in-mysql.html

15 April 2011 Situation: I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Zoho Deluge: Convert Xero Date (Unix Timestamp) to Standard Date Stringhttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-convert-xero-date-unix-timestamp-to-standard-date-string.html

04 May 2020 time researching it later. Why? I'm synchronizing Xero Invoices with Zoho CRM Invoices and noticed that Xero stores its dates in Unix Timestamps. How? We're going to filter out the unix seconds from the date provided by Xero then apply a toTime()...

• Type: Article
• Author: Joel Lipman
• Category: Zoho Deluge
• Language: *

Zoho Deluge: Generate a loop or list of any sizehttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-generate-a-list-of-any-size.html

21 October 2022 an array or list of any size. Why? My use case here is to run a schedule that will populate a list with the upcoming dates for the next week (and for any numbers of weeks thereafter). This needs to be dynamic as it is a schedule run daily checking the...

• Type: Article
• Author: Joel Lipman
• Category: Zoho Deluge
• Language: *

Formatting a date in an MDX queryhttps://joellipman.com/articles/else/database/t-sql/formatting-a-date-in-an-mdx-query.html

11 January 2011 DateFormat.LongTime) =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime) Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

DataJumble - Shuffling characters in a data valuehttps://joellipman.com/articles/else/database/t-sql/data-shuffling-function.html

01 August 2012 DECLARE @NewVal varchar(max); DECLARE @OrigLen int; DECLARE @CurrLen int; DECLARE @LoopCt int; DECLARE @Rand int; -- for dates/times DECLARE @MinYear int; DECLARE @MaxYear int; DECLARE @RandYear int; DECLARE @RandMonth int; DECLARE @RandDay int; DECLARE...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

DataScramble - Randomizing data rowshttps://joellipman.com/articles/else/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 DECLARE @OrigLen int; DECLARE @CurrLen int; DECLARE @LoopCt int; DECLARE @Rand int; DECLARE @RandString varchar(max); -- for dates/times DECLARE @MinYear int; DECLARE @MaxYear int; DECLARE @RandYear int; DECLARE @RandMonth int; DECLARE @RandDay int;...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

MySQL: Display Users and Duration in Matrix Timesheethttps://joellipman.com/articles/else/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 Data Activities by employees with start dates and end dates in a mySQL database. Objective User Mon Tue Wed Thu Fri Sat Sun Total --------------- ------- ------- ------- ------- ------- ------- ------- --------- Me 09:00 07:30 08:00 07:00 06:00 00:00...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

Add attribute xsl:nil=true on empty elements using XSLThttps://joellipman.com/articles/cms/website-development/xml/xslt/add-attribute-xsl-nil-true-on-empty-elements-using-xslt.html

18 August 2014 output blank values using single tags so we had to find a place to introduce it. On strings this has little worth, but on dates which could be NULL, this was necessary (unless we interpreted dates as strings which we don't want to do): -- SITS Output:...

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• Language: *

Excel PivotTable Filter List Orderinghttps://joellipman.com/component/content/article/excel-pivottable-filter-list-ordering.html?catid=110&Itemid=165

05 September 2011 against wall. Alternatively Change the SQL query so that the date returns in yyyy-mm-dd format (eg. 2012-03-04) Reverse Dates Order If you have a date filter list and want it sorted in descending order, I would recommend you return it in SQL date format...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

MySQL day of week ending on Fridayhttps://joellipman.com/articles/else/database/mysql/mysql-day-of-week-ending-on-friday.html

26 April 2012 day of the week. The Solution Where "givenDate" is the given date you have to calculate the last working day of: -- SAMPLE DATES ARE: -- -- 2012-04-27 10:00:00 -- is a Friday -- 2012-04-28 11:00:00 -- is a Saturday -- 2012-04-29 12:00:00 -- is a Sunday...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

AutoHotkey Format Date and Format Secondshttps://joellipman.com/component/content/article/autohotkey-format-date-and-format-seconds.html?catid=48&Itemid=165

20 September 2011 you can tell these are my messed up functions that convert dates into seconds and vice-versa. They're a little disorganised but they're the ones I copy and paste to my scripts then modify. In it's straightforward form FormatTime( TimeString, Format ) {...

• Type: Article
• Author: Joel Lipman
• Category: AutoHotkey
• Language: en-GB

Zoho Deluge - Some Useful Regular Expressionshttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-some-useful-regular-expressions.html

31 May 2019 // yields I am Joe Not a regular expression but something I use to pad months and dates in a date format: v_MyString = 2; // February v_FormattedString = leftpad(toString(v_MyString), 2).replaceAll(" ", "0"); // yields "02"

• Type: Article
• Author: Joel Lipman
• Category: Zoho Deluge
• Language: *