Microsoft SQL Server/Functions

min, max The functions Min() and Max() respectively return the minimum and the maximum of one field list. select min(Date) from Calendar where RDV = 'Important' cast Modify a variable type: cast(Champ as decimal(12, 6)) -- otherwise '9' > '10' convert Modify a variable type in first parameter, and its length in second. convert(varchar, Field1, 112) convert(datetime, Field2, 112) -- otherwise impossible to go through the calendar (eg: D + 1) Attention: all the variable types are not compatible between themman CONVERT. Problem examples: select Date1 from Table1 where Date1 between '01/10/2013' and '31/10/2013' Dates are not systematically recognized without convert. The solution is to store them in the datetime format: select Date1 from Table1 where Date1 between convert(varchar,'20131001',112) and convert(varchar,'20131031',112) On the other hand, if an above paragraph date is stored in varchar with slashes, it becomes mandatory to reformat it to be able to compare. Numerous date formats are availablehttp://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar. left, right, and substring Allow to cut strings according to some of their characters positionsman SUBSTRING. select substring('13/10/2013 00:09:19', 7, 4) -- returns the hour character after the seventh, so "2013" For example with the slashes date case above: select Date1 from Table1 where right(Date1, 4) + substring(Date1, 4, 2) + left(Date1, 2) between convert(varchar,'20131001',112) and convert(varchar,'20131031',112) replace and stuff Search and replace: allow to replace some string characters according to their valuesman STUFF. For example, to update a given folder pathman REPLACE : update Table1 SET Field1 = replace(Field1,'\Old_path\','\New_path\') where Field1 like '%\Old_path\%' isnull Returns true the variable is null. select Field1 = case when isnull(@Column,'')='' then '*' else @Column end from Table1 Dates Date format The function GETDATE is used to get the current date. To get another date in the good format, it's necessary to use CONVERT: select convert(smalldatetime, '2016-01-02', 121) Date cut The function DATEPART extracts a date part without specifying manually its positionman DATEPART. However, three functions allow to accelerate these extractions writing: -- Day select day(getdate()) -- Month select month(getdate()) -- Year select year(getdate()) -- Previous year select str(year(getdate()) - 1) Days addition and subtraction Herewith two dates manipulation functionshttp://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/: DATEDIFF calculates the interval between two datesman DATEDIFF. DATEADD returns the date resulting from another plus an intervalman DATEADD. -- Last day of the previous month SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) -- Last day of the current month SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) -- Last day of the previous month SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) Example: SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'20150101'),0)) as date gives: date 2014-12-31 23:59:59.000 References