Generate Academic Calendar using MySQLhttps://www.joellipman.com/articles/database/mysql/generate-academic-calendar-using-mysql.html

27 November 2012 12-Jul-2015 28 This query in T-SQL (SQL Server 2008 R2) for CMIS Facility SELECT calendar.WeekNumber AS AcademicWeek, CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' +...

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

Before and After, Highs and Lowshttps://www.joellipman.com/articles/microsoft/ssrs/before-and-after-highs-and-lows.html

03 August 2011 one RDL or SSRS Solution. I could do a dataset per SQL query but it doesn't seem that ideal. In Theory: SELECT ItemName, DATEPART(dayofyear, ItemDate) as DayOfYear, DATEPART(year, ItemYear) AS YearRun, COUNT(ItemName) AS Counter FROM TableName WHERE...

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

Cheat Sheet for mySQL vs t-SQLhttps://www.joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 DATALENGTH(string) Dates / Times Weekday Name DAYNAME(now()) DATENAME(dd, getdate()) Weekday Number DAYOFWEEK(now()) DATEPART(dw, getdate()) Month Name MONTHNAME(now()) DATENAME(mm, getdate()) Month Number MONTH(now()) DATEPART(mm, getdate()) European...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: *

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

14 January 2011 = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) --First day to display on calendar SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate) --Last day of month SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))...

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

SSIS Convert a string into a datehttps://www.joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 + " 00:00:00") Date back into String -- Converting yyyy-mm-dd hh:ii:ss to ddmmyyyy RIGHT("00" + (DT_STR,2,1252)DATEPART("dd",DateVerified), 2) + RIGHT("00" + (DT_STR,2,1252)DATEPART("mm",DateVerified), 2) + (DT_STR,4,1252)DATEPART("yyyy",DateVerified)

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

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

01 August 2012 BEGIN -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ago from today SET @MaxYear = DATEPART(YEAR,GETDATE())-18; -- 18 years ago from today -- GET...

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

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

07 December 2012 BEGIN -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ago from today SET @MaxYear = DATEPART(YEAR,GETDATE())-18; -- 18 years ago from today -- GET...

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

Zoho Deluge: Sort a Map by a specific fieldhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-sort-a-map-by-a-specific-field.html

29 January 2021 { // as this is a date from CRM (atomic date format), arrange so it can be sorted alphabetically in reverse order v_DatePart = v_SortingKey.getPrefix("T").toDate().toString("yyyy-MM-dd"); v_TimePart =...

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

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

11 January 2011 hour before I gave up trying to get SWITCH to work. Instead I cheated and got the ordinal in the Transact-SQL query: CASE DATEPART(DAY, [MyDateValue]) WHEN 1 THEN 'st' WHEN 21 THEN 'st' WHEN 31 THEN 'st' WHEN 2 THEN 'nd' WHEN 22 THEN 'nd' WHEN 3 THEN...

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