Documentation Home

Tip

All examples used are valid against the Microsoft Northwind Sample Database You can run the resulting SQL from the examples against Northwind in order to test and play with the SqlObjects library.

Functions

ABS

var sql = TSQL

    .SELECT()
      .ABS("[UnitsInStock]")
    .FROM("Products")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 ABS([UnitsInStock])
FROM [Products]

AVG

var sql = TSQL

    .SELECT()
      .AVG("[UnitPrice]")
    .FROM("Products")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 AVG([UnitPrice])
FROM [Products]

CEILING

var sql = TSQL

    .SELECT()
      .CEILING("[UnitPrice]")
    .FROM("Products")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 CEILING([UnitPrice])
FROM [Products]

COUNT

var sql = TSQL

    .SELECT()
      .COUNT("*")
    .FROM("Products")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 COUNT(*)
FROM [Products]

DATEADD

var sql = TSQL

    .Script(@"
      DECLARE @dateVar AS DATETIME
      SET @dateVar = GETDATE()
    ")
    .SELECT()
      .DATEADD(DateParts.Day, 1, new DateTime(2021, 1, 1)).AS("Result1")
      .DATEADD(DateParts.Day, 1, "@dateVar").AS("Result2")
      .DATEADD(DateParts.Day, 1, "[HireDate]").AS("Result3")
    .FROM("Employees")
    .Build()

;

Console.WriteLine(sql);

Output:

DECLARE @dateVar AS DATETIME
SET @dateVar = GETDATE()

SELECT
 DATEADD(Day, 1, '2021-01-01 00:00:00') AS [Result1],
 DATEADD(Day, 1, @dateVar) AS [Result2],
 DATEADD(Day, 1, [HireDate]) AS [Result3]
FROM [Employees]

DATEDIFF

var today = new DateTime(2021, 1, 1);
var weekAgo = today.AddDays(-7);

var sql = TSQL

    .Script(@"
      DECLARE @startDateVar DATETIME, @endDateVar DATETIME
      SET @startDateVar = '2020-01-15 00:00:00'
      SET @endDateVar = '2021-02-01 00:00:00'
    ")
    .SELECT()
      .DATEDIFF(DateParts.Day, weekAgo, today).AS("Result1")
      .DATEDIFF(DateParts.Day, "@startDateVar", today).AS("Result2")
      .DATEDIFF(DateParts.Day, weekAgo, "@endDateVar").AS("Result3")
      .DATEDIFF(DateParts.Day, "@startDateVar", "@endDateVar").AS("Result4")
      .DATEDIFF(DateParts.Day, "[HireDate]", today).AS("Result5")
      .DATEDIFF(DateParts.Day, "e", "HireDate", "@endDateVar").AS("Result6")
      .DATEDIFF(DateParts.Day, weekAgo, "e", "HireDate").AS("Result7")
      .DATEDIFF(DateParts.Day, "e", "BirthDate", "e", "HireDate").AS("Result8")
    .FROM("dbo", "Employees", "e")
    .Build()

;

Console.WriteLine(sql);

Output:

DECLARE @startDateVar DATETIME, @endDateVar DATETIME
SET @startDateVar = '2020-01-15 00:00:00'
SET @endDateVar = '2021-02-01 00:00:00'

SELECT
 DATEDIFF(Day, '2020-12-25 00:00:00', '2021-01-01 00:00:00') AS [Result1],
 DATEDIFF(Day, @startDateVar, '2021-01-01 00:00:00') AS [Result2],
 DATEDIFF(Day, '2020-12-25 00:00:00', @endDateVar) AS [Result3],
 DATEDIFF(Day, @startDateVar, @endDateVar) AS [Result4],
 DATEDIFF(Day, [HireDate], '2021-01-01 00:00:00') AS [Result5],
 DATEDIFF(Day, [e].[HireDate], @endDateVar) AS [Result6],
 DATEDIFF(Day, '2020-12-25 00:00:00', [e].[HireDate]) AS [Result7],
 DATEDIFF(Day, [e].[BirthDate], [e].[HireDate]) AS [Result8]
FROM [dbo].[Employees] e

DATENAME

var today = new DateTime(2021, 1, 1);

var sql = TSQL

    .Script(@"
      DECLARE @dateVar DATETIME
      SET @dateVar = GETDATE()
    ")
    .SELECT()
      .DATENAME(DateParts.Day, today).AS("Result1")
      .DATENAME(DateParts.Month, "@dateVar").AS("Result2")
      .DATENAME(DateParts.Month, "e", "HireDate").AS("Result3")
    .FROM("dbo", "Employees", "e")
    .Build()

;

Console.WriteLine(sql);

Output:

DECLARE @dateVar DATETIME
SET @dateVar = GETDATE()

SELECT
 DATENAME(Day, '2021-01-01 00:00:00') AS [Result1],
 DATENAME(Month, @dateVar) AS [Result2],
 DATENAME(Month, [e].[HireDate]) AS [Result3]
FROM [dbo].[Employees] e

DATEPART

var today = new DateTime(2021, 1, 1);

var sql = TSQL

    .Script(@"
      DECLARE @dateVar DATETIME
      SET @dateVar = GETDATE()
    ")
    .SELECT()
      .DATEPART(DateParts.Day, today).AS("Result1")
      .DATEPART(DateParts.Month, "@dateVar").AS("Result2")
      .DATEPART(DateParts.Month, "e", "HireDate").AS("Result3")
    .FROM("dbo", "Employees", "e")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 DATEPART(Day, '2021-01-01 00:00:00') AS [Result1],
 DATEPART(Month, @dateVar) AS [Result2],
 DATEPART(Month, [e].[HireDate]) AS [Result3]
FROM [dbo].[Employees] e

DAY

var today = new DateTime(2021, 1, 1);

var sql = TSQL

    .Script(@"
      DECLARE @dateVar DATETIME
      SET @dateVar = GETDATE()
    ")
    .SELECT()
      .DAY(today).AS("Result1")
      .DAY("@dateVar").AS("Result2")
      .DAY("e", "HireDate").AS("Result3")
    .FROM("dbo", "Employees", "e")
    .Build()
;

Console.WriteLine(sql);

Output:

DECLARE @dateVar DATETIME
SET @dateVar = GETDATE()

SELECT
 DAY('2021-01-01 00:00:00') AS [Result1],
 DAY(@dateVar) AS [Result2],
 DAY([e].[HireDate]) AS [Result3]
FROM [dbo].[Employees] e

FLOOR

var sql = TSQL

    .Script(@"
      DECLARE @decVar DECIMAL(18,2)
      SET @decVar = 9.99
    ")
    .SELECT()
      .FLOOR(9.99m).AS("Result1")
      .FLOOR("@decVar").AS("Result2")
      .FLOOR("p", "UnitPrice").AS("Result3")
    .FROM("dbo", "Products", "p")
    .Build()

;

Console.WriteLine(sql);

Output:

DECLARE @decVar DECIMAL(18,2)
SET @decVar = 9.99

SELECT
 FLOOR(9.99) AS [Result1],
 FLOOR(@decVar) AS [Result2],
 FLOOR([p].[UnitPrice]) AS [Result3]
FROM [dbo].[Products] p

GETDATE

var sql = TSQL

    .SELECT()
      .GETDATE().AS("CurrentDate")
      .COLUMN("HireDate")
    .FROM("Employees")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 GETDATE() AS [CurrentDate],
 [HireDate]
FROM [Employees]

ISNULL

var sql = TSQL

    .SELECT()
      .ISNULL("p", "UnitPrice", 0.00m)
      .ISNULL("UnitsOnOrder", 0)
      .ISNULL("@someVarThatMightBeNull", Guid.Empty)
    .FROM("dbo", "Products", "p")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 ISNULL([p].[UnitPrice], 0.00),
 ISNULL([UnitsOnOrder], 0),
 ISNULL(@someVarThatMightBeNull, '00000000-0000-0000-0000-000000000000')
FROM [dbo].[Products] p

MAX

var sql = TSQL

    .SELECT()
      .MAX("p", "UnitPrice")
      .MAX("UnitsOnOrder")
    .FROM("dbo", "Products", "p")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 MAX([p].[UnitPrice]),
 MAX(UnitsOnOrder)
FROM [dbo].[Products] p

MIN

var sql = TSQL

    .SELECT()
      .MIN("p", "UnitPrice")
      .MIN("UnitsOnOrder")
    .FROM("dbo", "Products", "p")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 MIN([p].[UnitPrice]),
 MIN(UnitsOnOrder)
FROM [dbo].[Products] p

MONTH

var today = new DateTime(2021, 1, 1);

var sql = TSQL

    .Script(@"
      DECLARE @dateVar DATETIME
      SET @dateVar = GETDATE()
    ")
    .SELECT()
      .MONTH(today).AS("Result1")
      .MONTH("@dateVar").AS("Result2")
      .MONTH("e", "HireDate").AS("Result3")
    .FROM("dbo", "Employees", "e")
    .Build()
;

Console.WriteLine(sql);

Output:

DECLARE @dateVar DATETIME
SET @dateVar = GETDATE()

SELECT
 MONTH('2021-01-01 00:00:00') AS [Result1],
 MONTH(@dateVar) AS [Result2],
 MONTH([e].[HireDate]) AS [Result3]
FROM [dbo].[Employees] e

SUM

var sql = TSQL

    .SELECT()
      .SUM("p", "UnitsOnOrder")
    .FROM("dbo", "Products", "p")
    .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 SUM([p].[UnitsOnOrder])
FROM [dbo].[Products] p

YEAR

var today = new DateTime(2021, 1, 1);

var sql = TSQL

    .Script(@"
      DECLARE @dateVar DATETIME
      SET @dateVar = GETDATE()
    ")
    .SELECT()
      .YEAR(today).AS("Result1")
      .YEAR("@dateVar").AS("Result2")
      .YEAR("e", "HireDate").AS("Result3")
    .FROM("dbo", "Employees", "e")
    .Build()
;

Console.WriteLine(sql);

Output:

DECLARE @dateVar DATETIME
SET @dateVar = GETDATE()

SELECT
 YEAR('2021-01-01 00:00:00') AS [Result1],
 YEAR(@dateVar) AS [Result2],
 YEAR([e].[HireDate]) AS [Result3]
FROM [dbo].[Employees] e