Search - Joel Lipman .Com

1. Zoho Deluge: Sort a Map by a specific field: / Zoho /; ... 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 = v_SortingKey.getSuffix("T").getPrefix("+").subString(0,5); v_SqlDateTime ...; Created on 29 January 2021
2. Zoho Deluge - Compare time and currenttime with timezone: / Zoho /; ... HH:mm:ss"); v_ExpiryTime = "2020-03-25T21:41:07+00:00"; v_ExpiryDatePart = v_ExpiryTime.subString(0,10); v_ExpiryTimePart = v_ExpiryTime.subString(11,19); v_ExpiryTimeCheck = v_ExpiryDatePart + " " ...; Created on 13 January 2020
3. SSIS Convert a string into a date: / SQL Server Integration Services /; ... 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)D ...; Created on 14 March 2013
4. Cheat Sheet for mySQL vs t-SQL: / Databases /; ... 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 Date ...; Created on 31 January 2013
5. DataScramble - Randomizing data rows: / Transact-SQL /; ... -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ...; Created on 07 December 2012
6. Generate Academic Calendar using MySQL: / MySQL /; ... (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), ...; Created on 27 November 2012
7. DataJumble - Shuffling characters in a data value: / Transact-SQL /; ... -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ...; Created on 01 August 2012
8. Before and After, Highs and Lows: / SQL Server Reporting Services /; ... ItemName, DATEPART(dayofyear, ItemDate) as DayOfYear, DATEPART(year, ItemYear) AS YearRun, COUNT(ItemName) AS Counter FROM TableName WHERE ItemDate BETWEEN '01/01/2010' ...; Created on 03 August 2011
9. SSRS / MDX Date Queries: / SQL Server Reporting Services /; ... DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())), "dd/MM/yyyy") // yields: 07/08/2011 This Month: -- Start Date (US format - mm/dd/yyyy) =DateAdd("d",1-DatePart("d",Today()),Today()) // yields ...; Created on 03 August 2011
10. SQL Calendar in Business Intelligence Development Studio: / Transact-SQL /; ... @StartDate DATETIME, @EndDate DATETIME --First day of current month SET @StartDate = 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 ...; Created on 14 January 2011
11. Formatting a date in an MDX query: / Transact-SQL /; ... but I spent over an 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' ...; Created on 11 January 2011