Microsoft SQL Server/Functions
min, max
[edit | edit source]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
[edit | edit source]Modify a variable type:
cast(Champ as decimal(12, 6)) -- otherwise '9' > '10'
convert
[edit | edit source]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 them[1].
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 available[2].
left, right, and substring
[edit | edit source]Allow to cut strings according to some of their characters positions[3].
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
[edit | edit source]Search and replace: allow to replace some string characters according to their values[4].
For example, to update a given folder path[5] :
update Table1
SET Field1 = replace(Field1,'\Old_path\','\New_path\')
where Field1 like '%\Old_path\%'
isnull
[edit | edit source]Returns true the variable is null.
select Field1 = case when isnull(@Column,'')='' then '*' else @Column end
from Table1
Dates
[edit | edit source]Date format
[edit | edit source]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
[edit | edit source]The function DATEPART
extracts a date part without specifying manually its position[6].
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
[edit | edit source]Herewith two dates manipulation functions[7]:
DATEDIFF
calculates the interval between two dates[8].DATEADD
returns the date resulting from another plus an interval[9].
-- 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