Misc Tips

SQL Server

Make a Copy of SQL Server Services Reports
Navigate to the location of the report, click Show details (right corner). Click the Edit icon next to the report. On the General tab under the "Report Definition" section, click "Edit", This prompts a Save dialogue. Click Update to replace the report definition

Find Max Date - Time from separate columns
Select Column1
, Convert(date, Max(DateAdd(d, DateDiff(d, 0, Cast(StartDate As datetime)), Cast(StartTime as datetime)) )) as StartDate
, Convert(time, Max(DateAdd(d, DateDiff(d, 0, Cast(StartDate As datetime)), Cast(StartTime as datetime)) )) as StartTime
From  [db_TGEDW_Reporting].[dbo].[tbl_SalesAgreementDM] 
Group By Column1
Convert numeric to Date to SQL Server Date Type
Convert(Date, Convert(varchar, numeric_column ))
Convert to Decimal Time
convert(decimal(5,2), datepart(minute, convert(time,[NumericHours] ) )  ) / 60
+  
convert(int, datepart(HOUR, convert(time,[Hours] ) )  ) as EmployeeTime
Remove leading zeros from a Character column
STRIP ( CHAR ( column ) , L ,  '0' )  
Multiple LEFT OUTER JOIN exmaple
FROM schema.TABLE1 A LEFT OUTER JOIN schema.TABLE2 B ON A.Key = B.Key
          LEFT OUTER JOIN schema.TABLE3 C ON A.Key = C.Key AND A.Key2 = C.Key2,
SELECT null as a column
SELECT Col1, Col2, cast(null as varchar) as Col3, FROM schema.table
Select Count, Distinct
SELECT Col1, Col2, Count(*) FROM schema.Table
Group By Col1, Col2
SQL Server-Remove time from a Date
DATEADD(dd, DATEDIFF(dd, 0, START_TIME), 0)
Not Null Grouping
AND NOT (A.COLUMN_1 IS NULL AND B.COLUMN_1  IS NULL AND C.COLUMN_1  
  IS NULL AND D.COLUMN_1  IS NULL)
SQL Server Date field in YYYY-MMDD format
Convert(Date, Convert(Varchar,AC#DTE))  

DB2 and iSeries UDB

Format a DB2 TimeStamp so hour and minutes are zero
TIMESTAMP(CURRENT DATE, '00:00:00')
OR
SELECT * FROM schema.table WHERE Date(Col_TimeStamp) = '2011-01-12'
Subtract MICROSECOND from Current TimeStamp-DB2
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
DB2 iSeries - Date To Char
 REPLACE(CHAR(CURRENT DATE, ISO),'-','')
DB2, select FIRST n rows
SELECT FROM schema.table FETCH FIRST 1000 ROWS ONLY; 
DB2 iSeries - Convert number to Date, WEEK from Date
SELECT TO_DATE(CHAR(datefield),'yyyymmdd'), week(TO_DATE(CHAR(datefield),'yyyymmdd'))
DB2 default NULL
 CASE LTRIM(RTRIM(ADRSTA))
       WHEN '' THEN Cast(NULL as char)
       Else LTRIM(RTRIM(ADRSTA))
  END as ADRSTA
Alter the Starting Value of a DB2 Identity Column
Alter Table schema.table Alter col_name Restart With numeric_value
Delete 2 years of data from table
DELETE FROM schema.Table_Name
WHERE ( Days (CURRENT DATE) - Days (Run_Date) ) > 365 * 2
Excel - substring from first non zero

used to get the SAP material number (possible 18 characters long)

=MID(A11, MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9},A11)),FIND({1,2,3,4,5,6,7,8,9},A11))), 18)
List 10 Customers by State.
SELECT c.CustomerName, c.State
    FROM (
        SELECT  CustomerName, State, Rank() 
          over (Partition BY State
                ORDER BY CustomerName ) AS Rank
        FROM tbl_Customer
        ) c WHERE Rank <= 10
Order by c.State,  c.CustomerName
WHERE Rank <= 2

Other

Cognos Data Manager - Convert To Date
ToDate( ToChar(ACDTE) , 'yyyymmdd')
Cognos Data Manager - Conditional Convert To Date
If(ACCDTE > 0, ToDate( ToChar( ACCDTE ) , 'yyyymmdd'), null)
Cognos Data Manager - Convert To Time
ToTime( LPad(ToChar(OPRFTIM),4,'0'), 'hhmi')
Windows Scheduling - Batch
schtasks /change /RU SYSTEM /DISABLE /TN WF_Recv_CoreAndSpice_AR
 
schtasks /change /RU SYSTEM /ENABLE /TN WF_Recv_CoreAndSpice_AR
Replacing a forward slash with a back slash
SET filepath=%1
SET filepath=%filepath:/=\%R
Citrix - Publish app with long URL
rem this script is used to open a url with a long path.  
Citrix has trouble opening apps with long command line parms
rem j.kinzer May 2012
option explicit
dim wshShell
set wshShell = CreateObject("wscript.shell")

dim vbQuote
dim sRunTime
vbQuote = Chr(34)

sRunTime = vbQuote & "C:\Program Files (x86)\Internet Explorer\iexplore.exe" & vbQuote & "  http://long url goes here"

wshShell.Run sRunTime, 1, vbFalse

set wshShell = nothing
iPhone Contact Syncing
with Google 2 step verification use, app specific password with spaces for CardDAV contact syncing also try making sure the App specific password name is the same as the name of the iphone exchange account

 

Convert JDE Julian Date to Other Format

See this IT Jungle article.

  • DB2 - (Dates beyond 2040 do not convert using this)
    DATE(DIGITS( DECIMAL( DateToConvert + 1900000,7,0)) ) AS CONVDATE
  • PostgreSQL (and DB2) -
    To_Date(To_Char( DateToConvert + 1900000, '9999999'), 'YYYYDDD') AS CONVDATE
Here is an on line converter.

 

Convert current date to JDE Julian Date:

  • DB2 - (1 * 100000 + (YEAR(CURRENT_DATE)-2000) * 1000 + DAYOFYEAR(CURRENT_DATE))
  • PostgreSQL -
    (extract('year' from CURRENT_DATE) - 1900) * 1000 +  extract('doy' from CURRENT_DATE)
SSIS - Conditionaly Convert number date to date data type
(XXDATE == 0 ? NULL(DT_DBDATE) : 
(DT_DBDATE)(SUBSTRING((DT_WSTR,8)XXDATE,1,4) + "-" 
  + SUBSTRING((DT_WSTR,8)XXDATE,5,2) + "-" 
  + SUBSTRING( (DT_WSTR,8)XXDATE,7,2))
)