Aligned Solutions Consulting

Handy SQL Statements & C# Expressions

Rick
11/29/2016 10:48 PM Comment(s)
Below is a living list of handy SQL Statements & C# Expressions I have come across to make things easier.  This is primarily a reference for myself. (Note: Copy & Pasting these will most likely not work since these are segments of SQL and often times you need to replace some values with values from your environment)

SQL

    • DATE & TIME(Depending on your environment NOW(), TODAY(), GETDATE() or someother date/time value maybe used)
      • Year To Date:
        • dateadd(yy, DATEDIFF(yy, 0, NOW()),0 )
      • Date Format CCYYMMDD:
        • convert(varchar, NOW(), 112)
      • Current Time Format HH:MM:SS for seconds from midnight:
        • CONVERT(varchar, DATEADD(ss, NOW(), 0), 108)
      • Current Time Format HHMMSS for seconds from midnight:
        • REPLACE(CONVERT(varchar, DATEADD(ss, NOW(), 0), 108), ':', ''
      • First Day of Current Year
        • DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
      • First Day of Last Year
        • DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())), 0)
    • STRINGS
      • Substring: MID() is NOT an T-SQL function, but SUBSTRING() is!
        • SUBSTRING(field, start position, length)
    • ROW COUNT (Windowing)
      • (ROW_Number() OVER(Partition by order by )) as
SPLITING STRINGS ON WORDS
    • SELECT IIF(LEN(Part.Description) < 30,             Part.Description,             LEFT(LEFT(Part.Description, 30), LEN(LEFT(Part.Description, 30)) - CHARINDEX(' ', REVERSE(LEFT(Part.Description, 29)))) ) AS , IIF(LEN(Part.Description) < 30,             '',       Substring(LEFT(Part.Description, 60),                 1+(LEN(LEFT(Part.Description, 30)) - CHARINDEX(' ', REVERSE(LEFT(Part.Description, 29)))), 30)) AS FROM SCHEMA.YOURTABLE
  • REMOVE ALPHAS FROM STRINGS
    • STUFF(YOURCOLUMN, PATINDEX('%%', YOURCOLUMN)-1, '')

C#

  • DateTime type to Formatted String: String.Format("{0:M/d/yyyy}", callContextBpmData.Date01)
  • Seconds (Integer) to short time formatted string: TimeSpan.FromSeconds(42200).ToString("t")
I am finding that the C# ToString() and String.Format() usage scope within Epicor to be quite baffling.  Not sure when to use which and many of the documentation sites are not very helpful in clearing this up.  Maybe I'll get it some day. Somewhat helpful ink:  http://www.csharp-examples.net/string-format-datetime/

Rick