Dot Net Dragon

Monday, December 17, 2012

How to create QR code or Barcode using Google Chart api

You can create a QR code on the fly with a URL GET request.

Overview

QR codes are a popular type of two-dimensional barcode. They are also known as hardlinks or physical world hyperlinks. QR Codes store up to 4,296 alphanumeric characters of arbitrary text. This text can be anything, for example URL, contact information, a telephone number, even a poem! QR codes can be read by an optical device with the appropriate software. Such devices range from dedicated QR code readers to mobile phones.

Syntax

Root URL: https://chart.googleapis.com/chart?
QR code requests support the following URL query parameters after the ? in the root URL:

Parameter	Required or Optional	Description
`cht=qr`	Required	Specifies a QR code.
`chs=<width>x<height>`	Required	Image size.
`chl=<data>`	Required	The data to encode. Data can be digits (0-9), alphanumeric characters, binary bytes of data, or Kanji. You cannot mix data types within a QR code. The data must be UTF-8 URL-encoded. Note that URLs have a 2K maximum length, so if you want to encode more than 2K bytes (minus the other URL characters), you will have to send your data using POST.
`choe=<output_encoding>`	Optional	How to encode the data in the QR code. Here are the available values: `UTF-8` [Default] `Shift_JIS` `ISO-8859-1`
`chld=<error_correction_level>\|<margin>`	Optional	error_correction_level - QR codes support four levels of error correction to enable recovery of missing, misread, or obscured data. Greater redundancy is achieved at the cost of being able to store less data. See the appendix for details. Here are the supported values: `L` - [Default] Allows recovery of up to 7% data loss `M` - Allows recovery of up to 15% data loss `Q` - Allows recovery of up to 25% data loss `H` - Allows recovery of up to 30% data loss

margin - The width of the white border around the data portion of the code. This is in rows, not in pixels. (See below to learn what rows are in a QR code.) The default value is 4.

Example:

https://chart.googleapis.com/chart?cht=qr&chl=Hello+world&choe=UTF-8&chs=200x200

Friday, December 7, 2012

Revert Back to IE 8 from Internet Explorer 9

If you’ve been trying out the Internet Explorer 9, you might want to revert back to IE 8 for some reason. Here we look at how to uninstall IE 9 and get IE 8 back.

Uninstall IE 9

First type appwiz.cpl into the Search box in the Start Menu and hit Enter which will open Programs and Features. Alternately you can open the Start Menu \ Control Panel \ Programs and Features.

Now in Programs and Features click View installed updates located on the left pane.

Then find Windows Internet Explorer 9, right-click, and uninstall.

Yes we’re sure…

Then a restart is require to complete the uninstall process.

This will restore you back to IE 8 and you will need to go through the process of setting it up again.

Wednesday, October 10, 2012

jqGrid and ASP.NET MVC

I've been using jqGrid a lot over last year. During this time I have created many helper classes for it. Recently I decided to put all those classes together, clean up, rewrite and publish for the community.
I made helper available as part of my Lib.Web.Mvc library, and you can download it here (source code included), or go for the NuGet package.

Now let me show you simple usage example. We will need a model class for our grid:

public class ProductViewModel

{

  #region Properties

  public int Id { get; set; }

  public string Name { get; set; }

  [JqGridColumnSortingName("SupplierId")]

  public string Supplier { get; set; }

  [JqGridColumnSortingName("CategoryId")]

  public string Category { get; set; }

  [DisplayName("Quantity Per Unit")]

  [JqGridColumnAlign(JqGridColumnAligns.Center)]

  public string QuantityPerUnit { get; set; }

  [DisplayName("Unit Price")]

  [JqGridColumnAlign(JqGridColumnAligns.Center)]

  public decimal? UnitPrice { get; set; }

  [DisplayName("Units In Stock")]

  [JqGridColumnAlign(JqGridColumnAligns.Center)]

  public short? UnitsInStock { get; set; }

  #endregion

  #region Constructor

  public ProductViewModel()

  { }

  public ProductViewModel(Product product)

  {

    this.Id = product.Id;

    this.Name = product.Name;

    this.Supplier = product.Supplier.Name;

    this.Category = product.Category.Name;

    this.QuantityPerUnit = product.QuantityPerUnit;

    this.UnitPrice = product.UnitPrice;

    this.UnitsInStock = product.UnitsInStock;

  }

  #endregion

}

Following standard DataAnnotations attributes are supported:

DisplayName - This will set the name for the column (default is property name).
DisplayFormat - Value set for NullDisplayText will be used as cell value if property value will be null. Value set for DataFormatString will be used for formatting property value (on server side).
HiddenInput - This will set JqGridColumnModel.Hidden to true.
Range - Value set for Maximum will be used for EditRules.MaxValue of editable column and value set Minimum for will be used for EditRules.MinValue.
Required - This will set EditRules.Required to true for editable column.
ScaffoldColumn - If set Scaffold to false, there will be no column created for property with this attribute.
StringLength - Value set for MaximumLength will be used for EditOptions.MaximumLength of editable column.

Additionaly the helper support those custom attributes (from Lib.Web.Mvc.JQuery.JqGrid.DataAnnotations namespace):

JqGridColumnLayout - This will set the layout attributes for the column (alignment, initial width etc.).
JqGridColumnSortable - This will set the sorting options for the column (by default every column is sortable with property name as sorting name).
JqGridColumnFormatter - This will set the predefined (with options) or custom formatter for the column.
JqGridColumnEditable - This will set the edit options for the column.
JqGridColumnSearchable - This will set the search options for the column.

When our model class is done, we can start creating the view. First we need to include some CSS and JavaScript files:

<link href="@Url.Content("~/Content/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />

<link href="@Url.Content("~/Content/jqGrid/jquery-ui-jqgrid.css")" rel="stylesheet" type="text/css" />

<script src="@Url.Content("~/Scripts/jquery-1.4.4.min.js")" type="text/javascript"></script>

<script src="@Url.Content("~/Scripts/jquery.jqGrid.locale-en-3.8.2.js")" type="text/javascript"></script>

<script src="@Url.Content("~/Scripts/jquery.jqGrid-3.8.2.min.js")" type="text/javascript"></script>

Then we can instantiate the helper and set desired options:

@{

  var grid = new Lib.Web.Mvc.JQuery.JqGrid.JqGridHelper<jqGrid.Models.ProductViewModel>("products",

    dataType: Lib.Web.Mvc.JQuery.JqGrid.JqGridDataTypes.Json,

    methodType: Lib.Web.Mvc.JQuery.JqGrid.JqGridMethodTypes.Post,

    pager: true,

    rowsNumber: 10,

    sortingName: "Id",

    sortingOrder: Lib.Web.Mvc.JQuery.JqGrid.JqGridSortingOrders.Asc,

    url: Url.Action("Products"),

    viewRecords: true

  );

}

This instance can be used for rendering HTML and JavaScript required by jqGrid:

@grid.GetHtml()

...

<script type="text/javascript">

  $(document).ready(function () {

    @grid.GetJavaScript()

  });

</script>

All we have to do now is creating an action method, which will provide data for our grid:

[AcceptVerbs(HttpVerbs.Post)]

public ActionResult Products(JqGridRequest request)

{

  int totalRecordsCount = _productsRepository.GetCount();

  JqGridResponse response = new JqGridResponse()

  {

    TotalPagesCount = (int)Math.Ceiling((float)totalRecordsCount / (float)request.RecordsCount),

    PageIndex = request.PageIndex,

    TotalRecordsCount = totalRecordsCount

  };

  response.Records.AddRange(from product in _productsRepository.FindRange(String.Format("{0} {1}", request.SortingName, request.SortingOrder), request.PageIndex * request.RecordsCount, request.RecordsCount)

                            select new JqGridRecord<ProductViewModel>(Convert.ToString(product.Id), new ProductViewModel(product)));

  return new JqGridJsonResult() { Data= response };

}

When you run it, you should see something similar to this:

You can download a sample project for this helper, which contains following examples:

Basics & Formatting
Configuration Import/Export
Cell Editing
CRUD - Inline Editing
CRUD - Form Editing
Searching - Single
Searching - Toolbar
Searching - Custom
Searching - Advanced
Subgrid
TreeGrid

The helper doesn't support all the functionality of jqGrid, it's just a compilation of what I've been using. There is also no roadmap for it at the moment.
Refrences: http://tpeczek.blogspot.in/2011/03/jqgrid-and-aspnet-mvc-strongly-typed.html

SQL Date time Coversion String date convert formats

Execute the following T-SQL scripts in Microsoft SQL Server Management Studio (SSMS) Query Editor to demonstrate T-SQL CONVERT and CAST functions in transforming string SQL date formats, string time & string datetime data to datetime data type. Practical examples for T-SQL DATE / DATETIME functions.

-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

-- Without century (yy) string date conversion - convert string to datetime function

SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

-- YYYYMMDD ISO date format works at any language setting - international standard

SELECT convert(datetime, '20161023')

SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500

-- Style 126 is ISO 8601 format: international standard - works with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)

-- 2008-10-23 18:52:47.513

SELECT convert(datetime, N'23 شوال 1429 6:52:47:513PM', 130) -- Islamic/Hijri date

SELECT convert(datetime, '23/10/1429 6:52:47:513PM', 131) -- Islamic/Hijri date

-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions

-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan

SELECT convert(datetime, '161023', 12) -- yymmdd ISO

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt

SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

------------

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss

-- String to datetime - mssql datetime - sql convert date - sql concatenate string

DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)

SELECT @DateValue = '20120718',

@TimeValue = '211920'

SELECT @DateTimeValue =

convert(varchar, convert(datetime, @DateValue), 111)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, 2)

SELECT

DateInput = @DateValue, TimeInput = @TimeValue,

DateTimeOutput = @DateTimeValue;

DateInput TimeInput DateTimeOutput

20120718 211920 2012/07/18 21:19:20 */

/* DATETIME 8 bytes internal storage structure
o 1st 4 bytes: number of days after the base date 1900-01-01

o 2nd 4 bytes: number of clock-ticks (3.33 milliseconds) since midnight

     DATE 3 bytes internal storage structure
   o 3 bytes integer: number of days after the first date 0001-01-01
   o Note: hex byte order reversed

SMALLDATETIME 4 bytes internal storage structure
o 1st 2 bytes: number of days after the base date 1900-01-01

o 2nd 2 bytes: number of minutes since midnight */

SELECT CONVERT(binary(8), getdate()) -- 0x00009E4D 00C01272

SELECT CONVERT(binary(4), convert(smalldatetime,getdate())) -- 0x9E4D 02BC

-- This is how a datetime looks in 8 bytes

DECLARE @dtHex binary(8)= 0x00009966002d3344;

DECLARE @dt datetime = @dtHex

SELECT @dt -- 2007-07-09 02:44:34.147

------------ */

------------

-- SQL Server 2012 New Date & Time Related Functions

------------

SELECT DATEFROMPARTS ( 2016, 10, 23 ) AS RealDate; -- 2016-10-23

SELECT DATETIMEFROMPARTS ( 2016, 10, 23, 10, 10, 10, 500 ) AS RealDateTime; -- 2016-10-23 10:10:10.500

SELECT EOMONTH('20140201'); -- 2014-02-28

SELECT EOMONTH('20160201'); -- 2016-02-29

SELECT EOMONTH('20160201',1); -- 2016-03-31

SELECT FORMAT ( getdate(), 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ); -- 2016/07/30 03:39:48 AM

SELECT FORMAT ( getdate(), 'd', 'en-US' ); -- 7/30/2016

SELECT PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time];

-- 2014-12-13 00:00:00.000

SELECT TRY_PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time];

-- 2014-12-13 00:00:00.000

SELECT TRY_CONVERT(datetime, '13 December 2014' ) AS [Date&Time]; -- 2014-12-13 00:00:00.000

------------

-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm

DECLARE @Seconds INT

SET @Seconds = 20000

SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)

/* HH MM SS

5 33 20 */

------------

-- SQL Server Date Only from DATETIME column - get date only

-- T-SQL just date - truncate time from datetime - remove time part

------------

DECLARE @Now datetime = CURRENT_TIMESTAMP -- getdate()

SELECT DateAndTime = @Now -- Date portion and Time portion

,DateString = REPLACE(LEFT(CONVERT (varchar, @Now, 112),10),' ','-')

,[Date] = CONVERT(DATE, @Now) -- SQL Server 2008 and on - date part

,Midnight1 = dateadd(day, datediff(day,0, @Now), 0)

,Midnight2 = CONVERT(DATETIME,CONVERT(int, @Now))

,Midnight3 = CONVERT(DATETIME,CONVERT(BIGINT,@Now) & (POWER(Convert(bigint,2),32)-1))

/* DateAndTime DateString Date Midnight1 Midnight2 Midnight3

2010-11-02 08:00:33.657 20101102 2010-11-02 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 */

------------

-- SQL Server 2008 convert datetime to date - sql yyyy mm dd

SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),

Today = CONVERT(date, getdate())

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY newid();

/* OrderDate Today

2004-02-15 2012-06-18 .....*/

------------

-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

/* YYYY/MM/DD

2015/07/11 */

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]

/* YYYYMMDD

20150711 */

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD]

/* YYYY MM DD

2015 07 11 */
-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM

PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM

------------

-- UNIX to SQL Server datetime conversion

declare @UNIX bigint = 1477216861;

select dateadd(ss,@UNIX,'19700101'); -- 2016-10-23 10:01:01.000
------------

-- String to date conversion - sql date yyyy mm dd - sql date formatting

-- SQL Server cast string to date - sql convert date to datetime

SELECT [Date] = CAST (@DateValue AS datetime)

-- 2012-07-18 00:00:00.000

-- SQL convert string date to different style - sql date string formatting

SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)

-- May 8 2014 12:00AM

-- SQL Server convert date to integer

DECLARE @Date datetime; SET @Date = getdate();

SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);

-- Result: 20161225

-- SQL Server convert integer to datetime

DECLARE @iDate int

SET @iDate = 20151225

SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)

-- 2015-12-25 00:00:00.000

-- Alternates: date-only datetime values

-- SQL Server floor date - sql convert datetime

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))

-- SQL Server cast string to datetime

-- SQL Server datetime to string convert

SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)

-- SQL Server dateadd function - T-SQL datediff function

-- SQL strip time from date - MSSQL strip time from datetime

SELECT getdate() ,dateadd(dd, datediff(dd, 0, getdate()), 0)

-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000

-- String date - 10 bytes of storage

SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)

SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)

-- Same results: 01-02-2012

-- SQL Server cast datetime as string - sql datetime formatting

SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM

----------

-- SQL date range BETWEEN operator

----------

-- SQL date range select - date range search - T-SQL date range query

-- Count Sales Orders for 2003 OCT-NOV

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = convert(DATETIME,'10/01/2003',101)

SET @EndDate = convert(DATETIME,'11/30/2003',101)

SELECT @StartDate, @EndDate

-- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000

SELECT dateadd(DAY,1,@EndDate),

dateadd(ms,-3,dateadd(DAY,1,@EndDate))

-- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997

-- MSSQL date range select using >= and <

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM Sales.SalesOrderHeader

WHERE OrderDate >= @StartDate AND OrderDate < dateadd(DAY,1,@EndDate)

/* Sales Orders for 2003 OCT-NOV

3668 */

-- Equivalent date range query using BETWEEN comparison

-- It requires a bit of trick programming

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM Sales.SalesOrderHeader

WHERE OrderDate BETWEEN @StartDate AND dateadd(ms,-3,dateadd(DAY,1,@EndDate))

-- 3668

USE AdventureWorks;

-- SQL between string dates

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108

-- SQL BETWEEN dates without time - time stripped - time removed - date part only

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE datediff(dd,0,OrderDate)

BETWEEN datediff(dd,0,'20040201 12:11:39') AND datediff(dd,0,'20040210 14:33:19')

-- 108

-- BETWEEN is equivalent to >=...AND....<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'

/* Orders with OrderDates

'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)

'2004-02-10 00:01:00.000' - 1 minute after midnight

'2004-02-10 01:00:00.000' - 1 hour after midnight

are not included in the two queries above. */

-- To include the entire day of 2004-02-10 use:

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= '20040201' AND OrderDate < '20040211'

----------

-- Calculate week ranges in a year

----------

DECLARE @Year INT = '2016';

WITH cteDays AS (SELECT DayOfYear=Dateadd(dd, number,

CONVERT(DATE, CONVERT(char(4),@Year)+'0101'))

FROM master.dbo.spt_values WHERE type='P'),

CTE AS (SELECT DayOfYear, WeekOfYear=DATEPART(week,DayOfYear)

FROM cteDays WHERE YEAR(DayOfYear)= @YEAR)

SELECT WeekOfYear, StartOfWeek=MIN(DayOfYear), EndOfWeek=MAX(DayOfYear)

FROM CTE GROUP BY WeekOfYear ORDER BY WeekOfYear

------------

-- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions

------------

DECLARE @StringDate varchar(32)

SET @StringDate = '2011-03-15 18:50'

IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)

PRINT 'VALID DATE: ' + @StringDate

ELSE

PRINT 'INVALID DATE: ' + @StringDate

-- Result: VALID DATE: 2011-03-15 18:50

DECLARE @StringDate varchar(32)

SET @StringDate = '20112-03-15 18:50'

IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)

PRINT 'VALID DATE: ' + @StringDate

ELSE PRINT 'INVALID DATE: ' + @StringDate

-- Result: INVALID DATE: 20112-03-15 18:50

-- First and last day of date periods - SQL Server 2008 and on code

DECLARE @Date DATE = '20161023'

SELECT ReferenceDate = @Date

SELECT FirstDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date),0))

SELECT LastDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date)+1,-1))

SELECT FDofSemester = CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2),0))

SELECT LastDayOfSemester

= CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2)+2,-1))

SELECT FirstDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0, @Date),0))

-- 2016-10-01

SELECT LastDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0,@Date)+1,-1))

-- 2016-12-31

SELECT FirstDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date),0))

SELECT LastDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date)+1,-1))

SELECT FirstDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date),0))

SELECT LastDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date)+1,-1))

-- 2016-10-30

-- Month sequence generator - sequential numbers / dates

DECLARE @Date date = '2000-01-01'

SELECT MonthStart=dateadd(MM, number, @Date)

FROM master.dbo.spt_values

WHERE type='P' AND dateadd(MM, number, @Date) <= CURRENT_TIMESTAMP

ORDER BY MonthStart

/* MonthStart

2000-01-01

2000-02-01

2000-03-01 ....*/

------------

-- Selected named date styles
------------

DECLARE @DateTimeValue varchar(32)

-- US-Style

SELECT @DateTimeValue = '10/23/2016'

SELECT StringDate=@DateTimeValue,

[US-Style] = CONVERT(datetime, @DatetimeValue)

SELECT @DateTimeValue = '10/23/2016 23:01:05'

SELECT StringDate = @DateTimeValue,

[US-Style] = CONVERT(datetime, @DatetimeValue)

-- UK-Style, British/French - convert string to datetime sql

-- sql convert string to datetime

SELECT @DateTimeValue = '23/10/16 23:01:05'

SELECT StringDate = @DateTimeValue,

[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)

SELECT @DateTimeValue = '23/10/2016 04:01 PM'

SELECT StringDate = @DateTimeValue,

[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)

-- German-Style

SELECT @DateTimeValue = '23.10.16 23:01:05'

SELECT StringDate = @DateTimeValue,

[German-Style] = CONVERT(datetime, @DatetimeValue, 4)

SELECT @DateTimeValue = '23.10.2016 04:01 PM'

SELECT StringDate = @DateTimeValue,

[German-Style] = CONVERT(datetime, @DatetimeValue, 104)

------------

-- Double conversion to US-Style 107 with century: Oct 23, 2016

SET @DateTimeValue='10/23/16'

SELECT StringDate=@DateTimeValue,

[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)

-- Using DATEFORMAT - UK-Style - SQL dateformat

SET @DateTimeValue='23/10/16'

SET DATEFORMAT dmy

SELECT StringDate=@DateTimeValue,

[Date Time] = CONVERT(datetime, @DatetimeValue)

-- Using DATEFORMAT - US-Style

SET DATEFORMAT mdy
-- Finding out date format for a session

SELECT session_id, date_format from sys.dm_exec_sessions

------------

-- Convert date string from DD/MM/YYYY UK format to MM/DD/YYYY US format
DECLARE @UKdate char(10) = '15/03/2016'
SELECT CONVERT(CHAR(10), CONVERT(datetime, @UKdate,103),101)

-- 03/15/2016

-- DATEPART datetime function example - SQL Server datetime functions

SELECT * FROM Northwind.dbo.Orders

WHERE DATEPART(YEAR, OrderDate) = '1996' AND

DATEPART(MONTH,OrderDate) = '07' AND

DATEPART(DAY, OrderDate) = '10'

-- Alternate syntax for DATEPART example

SELECT * FROM Northwind.dbo.Orders

WHERE YEAR(OrderDate) = '1996' AND

MONTH(OrderDate) = '07' AND

DAY(OrderDate) = '10'

------------

-- T-SQL calculate the number of business days function / UDF - exclude SAT & SUN

------------

CREATE FUNCTION fnBusinessDays (@StartDate DATETIME, @EndDate DATETIME)

RETURNS INT AS

BEGIN

IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)

DECLARE @i INT = 0;

WHILE (@StartDate <= @EndDate)

BEGIN

SET @i = @i + CASE

WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1

ELSE 0

END

SET @StartDate = @StartDate + 1

END -- while

RETURN (@i)

END -- function

SELECT dbo.fnBusinessDays('2016-01-01','2016-12-31')

-- 261

------------

-- T-SQL DATENAME function usage for weekdays

SELECT DayName=DATENAME(weekday, OrderDate), SalesPerWeekDay = COUNT(*)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)

ORDER BY DATEPART(weekday,OrderDate)

/* DayName SalesPerWeekDay

Sunday 4482

Monday 4591

Tuesday 4346.... */

-- DATENAME application for months

SELECT MonthName=DATENAME(month, OrderDate), SalesPerMonth = COUNT(*)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate) ORDER BY MONTH(OrderDate)

/* MonthName SalesPerMonth

January 2483

February 2686

March 2750

April 2740.... */

-- Getting month name from month number

SELECT DATENAME(MM,dateadd(MM,7,-1)) -- July

ARTICLE - Essential SQL Server Date, Time and DateTime Functions
ARTICLE - Demystifying the SQL Server DATETIME Datatype

------------
-- Extract string date from text with PATINDEX pattern matching

-- Apply sql server string to date conversion

------------

USE tempdb;

CREATE TABLE InsiderTransaction (

InsiderTransactionID int identity primary key,

TradeDate datetime,

TradeMsg varchar(256),

ModifiedDate datetime default (getdate()))

-- Populate table with dummy data