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))
SQL Server Update in "chunks"
The following tip was stolen from the internet here 
WHILE 1 = 1
BEGIN;
  UPDATE TOP(1000) dest
     SET dest.Name = source.Name
    FROM business_table dest
   INNER JOIN temp_table source ON source.ID = dest.ID
   WHERE dest.name IS NULL;

  IF @@rowcount < 1 BREAK;

  WAITFOR DELAY 00:00:01;
END;

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 and Int
 REPLACE(CHAR(CURRENT DATE, ISO),'-','') AS strCurrentDate
 int(replace(char(current date, ISO),'-','')) AS intCurrentDate
 int(replace(char(current date - 1 day, ISO),'-','')) AS intYesterday
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
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
JDBC - Translate EBCDIC

Usually the Toolbox JDBC driver (for DbVisualizer and SQuirrel) will translate EBCDIC characters to Unicode Strings automatically, if not then the field on the IBM database is tagged with CCSID 65535. The Toolbox JDBC driver recognizes this CCSID as a field that should not be translated.
Set the "translate binary" connection property to "true"

;translate binary=true

Other

Cognos Data Manager - Convert To Date
ToDate( ToChar(ACDTE) , 'yyyymmdd')
DOS

Find large file system files: www.windows-commandline.com/find-large-files/

forfiles /S /M * /C "cmd /c if @fsize GEQ 10485760 echo @path"
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 Server

Cab files filling TEMP folder.

  1. Delete the .log files from %systemroot%\Logs\CBS (which prevents the .cab files from being regenerated).
  2. Delete the .cab files from %temp% (which clears space on the drive).
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
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)
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 - Conditionally 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))
)
SSIS - Conditionally check/replace a date
(DT_STR,10,1252)(DT_DBDATE)GetDate() >= "2019-01-25" ? GetDate() : (DT_DBDATE)"2019-01-25"
SSIS - In an Expression, output Date-Time as Date only
 (DT_STR,10,1252)(DT_DBDATE)@[User::dtNextProcessedDate]

Subscribe to: Posts (Atom)