Transact-SQL commands and functions

From DataSelf Knowledge Base
Jump to navigation Jump to search

Convert Accpac Numeric into Date

Convert Accpac YYYYMMDD numeric fields into date:

CASE WHEN INVDATE = 0 then NULL else CONVERT(DATETIME, CONVERT(CHAR(8), INVDATE))END

(Usually we just do this in the ETL, same formula: convert to String and put it into a Date field)

Convert Date type into String for easier viewing

http://msdn.microsoft.com/en-us/library/ms187928(v=SQL.105).aspx

ConvertDate.PNG

IsNumeric

ISNUMERIC (fieldname) returns 0 = NO, 1 = Yes
Convert “Weight” text field into numeric:
CASE WHEN isnumeric(Weight) = 1 THEN CONVERT (decimal(10 , 4) , weight) ELSE 0 END