Tuesday, May 6, 2014

Button with in Anchor tag works in IE8 and 7 for MVC

Button with in Anchor tag works in IE8 and 7 for MVC. Just add the below highlighted onclick="javascript:window.location=this.href"     as shown below in the "a" tag.

 <a href="@Url.Action("EventReg", "Events", new {Pgm_ID = ViewBag.PgmID})" onclick="javascript:window.location=this.href">
        <button>Register</button>
       </a>    

USEFUL MS SQL STATEMENT for Triggers and Constraints

------------
-- Disable all triggers and constraints in a database
------------

USE CopyOfAdventureWorks;

-- SQL disable all triggers
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

-- SQL disable all constraints
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

-- Enable all triggers on a table
ALTER TABLE Production.Product ENABLE TRIGGER ALL

-- Enable all check contraints on a table
ALTER TABLE Production.Product CHECK CONSTRAINT ALL
GO
------------
-- Single constraint disable and enable
------------
USE CopyOfAdventureWorks;
-- SQL disable constraint
ALTER TABLE Production.Product NOCHECK CONSTRAINT CK_Product_DaysToManufacture
GO

-- SQL enable constraint
ALTER TABLE Production.Product CHECK CONSTRAINT CK_Product_DaysToManufacture

GO

-- SQL enable constraint with check of current data
ALTER TABLE Production.Product WITH CHECK
CHECK CONSTRAINT CK_Product_DaysToManufacture
GO

-- SQL enable constraint with no check of current data
ALTER TABLE Production.Product WITH NOCHECK
CHECK CONSTRAINT CK_Product_DaysToManufacture

-- Check integrity of all constraints on a table
DBCC CHECKCONSTRAINTS('Production.Product');
GO
/* DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
*/

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

------------
-- Single trigger disable and enable
------------
-- SQL disable trigger
ALTER TABLE Sales.SalesOrderHeader DISABLE TRIGGER uSalesOrderHeader
GO

-- SQL enable trigger
ALTER TABLE Sales.SalesOrderHeader ENABLE TRIGGER uSalesOrderHeader
GO
------------
------------
-- Create a check constraint for a table and enable it
------------
-- SQL create check constraint
-- Range constraint - column value must be between 0 and 100
USE AdventureWorks;
ALTER TABLE [Production].[ProductInventory] WITH CHECK
ADD CONSTRAINT [CK_ProductInventory_Bin]
CHECK (([Bin]>=(0) AND [Bin]<=(100)))
GO

-- SQL enable check constraint
ALTER TABLE [Production].[ProductInventory]
CHECK CONSTRAINT [CK_ProductInventory_Bin]
GO

To Make IE 8 or lower version to support HTML5 Elements

To Make IE 8 or lower version to support HTML5 Elements. Add the below code snippet in the start page or master page.

@*----------- DONT Remove the bolow codes until endif]--> which is not really commented ---------- *@
    <!--[if lt IE 9]>
<script>
  var e = ("abbr,article,aside,audio,canvas,datalist,details," +
    "figure,footer,header,hgroup,mark,menu,meter,nav,output," +
    "progress,section,time,video").split(',');
  for (var i = 0; i < e.length; i++) {
    document.createElement(e[i]);
  }
</script>
<![endif]-->

---------------------------OR-------------------------
Create the html5.js and call that in your page as shown below. The html5.js code snippet has been highlighted below.

<script src="@Url.Content("~/Scripts/html5.js")" type="text/javascript" ></script>

html5.js

/*
 HTML5 
*/
(function(l,f){function m(){var a=e.elements;return"string"==typeof a?a.split(" "):a}function i(a){var b=n[a[o]];b||(b={},h++,a[o]=h,n[h]=b);return b}function p(a,b,c){b||(b=f);if(g)return b.createElement(a);c||(c=i(b));b=c.cache[a]?c.cache[a].cloneNode():r.test(a)?(c.cache[a]=c.createElem(a)).cloneNode():c.createElem(a);return b.canHaveChildren&&!s.test(a)?c.frag.appendChild(b):b}function t(a,b){if(!b.cache)b.cache={},b.createElem=a.createElement,b.createFrag=a.createDocumentFragment,b.frag=b.createFrag();
a.createElement=function(c){return!e.shivMethods?b.createElem(c):p(c,a,b)};a.createDocumentFragment=Function("h,f","return function(){var n=f.cloneNode(),c=n.createElement;h.shivMethods&&("+m().join().replace(/[\w\-]+/g,function(a){b.createElem(a);b.frag.createElement(a);return'c("'+a+'")'})+");return n}")(e,b.frag)}function q(a){a||(a=f);var b=i(a);if(e.shivCSS&&!j&&!b.hasCSS){var c,d=a;c=d.createElement("p");d=d.getElementsByTagName("head")[0]||d.documentElement;c.innerHTML="x<style>article,aside,dialog,figcaption,figure,footer,header,hgroup,main,nav,section{display:block}mark{background:#FF0;color:#000}template{display:none}</style>";
c=d.insertBefore(c.lastChild,d.firstChild);b.hasCSS=!!c}g||t(a,b);return a}var k=l.html5||{},s=/^<|^(?:button|map|select|textarea|object|iframe|option|optgroup)$/i,r=/^(?:a|b|code|div|fieldset|h1|h2|h3|h4|h5|h6|i|label|li|ol|p|q|span|strong|style|table|tbody|td|th|tr|ul)$/i,j,o="_html5shiv",h=0,n={},g;(function(){try{var a=f.createElement("a");a.innerHTML="<xyz></xyz>";j="hidden"in a;var b;if(!(b=1==a.childNodes.length)){f.createElement("a");var c=f.createDocumentFragment();b="undefined"==typeof c.cloneNode||
"undefined"==typeof c.createDocumentFragment||"undefined"==typeof c.createElement}g=b}catch(d){g=j=!0}})();var e={elements:k.elements||"abbr article aside audio bdi canvas data datalist details dialog figcaption figure footer header hgroup main mark meter nav output progress section summary template time video",version:"3.7.0",shivCSS:!1!==k.shivCSS,supportsUnknownElements:g,shivMethods:!1!==k.shivMethods,type:"default",shivDocument:q,createElement:p,createDocumentFragment:function(a,b){a||(a=f);
if(g)return a.createDocumentFragment();for(var b=b||i(a),c=b.frag.cloneNode(),d=0,e=m(),h=e.length;d<h;d++)c.createElement(e[d]);return c}};l.html5=e;q(f)})(this,document);

Tuesday, April 15, 2014

jquery-simple-datetimepicker appendDtpicker to get current date from textbox

jquery-simple-datetimepicker appendDtpicker to get current date from textbox      

   $(function () {            
             $('#img_strdate').click(function () {   
//get the date from text box txtStrDate and save the date in YYYY-MM-DD HH:MM format              
                 var str2 = new Date();;
                 if ($("#<%=txtStrDate.ClientID %>").val() != null && $("#<%=txtStrDate.ClientID %>").val() != "") {
                     str2 = $("#<%=txtStrDate.ClientID %>").val();
                 }
                 var re = /^(\d{1,2})[-/](\d{1,2})[-/](\d{2,4}) (\d{1,2}):(\d{1,2})$/;
                 var m = re.exec(str2);
                 var currentDate = m[3]+'-'+m[2]+'-'+m[1] + ' ' + m[4]+':'+m[5];
                 $("#<%=txtStrDate.ClientID %>").val('');
                 $("#<%=txtStrDate.ClientID %>").appendDtpicker({
                     "dateFormat": "DD/MM/YYYY hh:mm",                
                     //"dateOnly": true,                       
                     //"current": '2012-01-01 00:00',
              //get the current date of calender will be updated from text box. And it will not go to today's date
                     "current": currentDate,
                     "onInit": function (handler) {                        
                         handler.show();                         
                     },                   
                     "onHide": function (handler) {
                         var str1 = $("#<%=txtStrDate.ClientID %>").val();
                         var d = Date.parseExact(str1, 'dd/MM/yyyy H:mm');
                         var no_days = parseInt($("#<%=txt_dur.ClientID %>").val());
                         // window.alert(d);              
                         if ($("#<%=rd_yes.ClientID %>").is(':checked')) {
                             $("#<%=txtEndDate.ClientID %>").val(d.add({ days: no_days }).toString('dd/MM/yyyy 23:59'));
                         }
                         else {
                             $("#<%=txtEndDate.ClientID %>").val(d.add({ minutes: no_days }).toString('dd/MM/yyyy HH:mm'));
                         }
                         handler.destroy();
                     }
                 });
             });

The Datetimepicker source location: https://github.com/mugifly/jquery-simple-datetimepicker

Tuesday, December 20, 2011

How To Choose Between 32-bit & 64-bit Windows 7 Operating Systems?

Windows 7 launches this week and those waiting to upgrade should first decide if they want to install the Windows 7 64-bit (x64) version or stick with 32-bit (x86) Windows. Why? Even if you install 32-bit initially, it should be possible to upgrade to 64-bit later, right? Wrong.

You can’t upgrade from 32-bit to Windows 7 64-bit without doing a fresh “clean” install, which requires you to reinstall all applications from scratch. Therefore, you need to decide on either 32-bit or 64-bit before you install Windows 7.

While I recommend you go with 64-bit, read through this article to make an informed decision.

Background: What is 32-bit and 64-bit?
32-bit and 64-bit are computer architectures that specify the length of data types and addresses that are supported. What this means for the average user is how much memory can be used effectively and how powerful the number-crunching capacity of the CPU is.

Since Windows XP was originally only released as a 32-bit operating system, and because older hardware have 32-bit device drivers, application development on the Windows platform has been slow in moving to 64-bit. However, after both Windows XP and Vista were released in 64-bit, Microsoft is now pushing 64-bit strongly, and you can expect application developers to take advantage of 64-bit computing soon.



Should You Buy 32-bit or 64-bit Windows 7?
Fortunately, you do not need to worry about all this when purchasing a computer or Windows 7. If you are buying a new PC from a vendor, it will ship with 64-bit Windows pre-installed if the configuration supports 64-bit. If you buy retail boxed versions of Windows 7, they will include both 32-bit and 64-bit editions, except for the Home Basic edition.

Advantages of 64-bit
There are several benefits of going to Windows 7 64-bit:

■With 32-bit Windows, you can use a maximum of 4GB RAM. 64-bit Windows 7 runs very fast with 4GB and you can upgrade your RAM to 8 or 16 GB later, making your system future-proof.
■A 32-bit OS can theoretically use up to 4 GB of RAM, but 32-bit versions of Windows Vista and Windows 7 see a maximum of 3.12 GB. With 64-bit Windows 7, you can use the full 4GB RAM.
■You get better security with 64-bit Windows. All 64-bit device drivers are digitally signed, which means you will not have random crashes. You also get more advanced security features like Kernel Patch Protection with 64-bit Windows 7.
■Since 64-bit systems process more information and support greater RAM, Windows 7 is more responsive when you are running complex applications or many applications simultaneously. If you use graphics applications like Photoshop, video editing, games, CAD, etc., you should go 64-bit.
■Not all applications have 64-bit versions that take advantage of the 64-bit architecture, but you can expect more of them after Windows 7 goes mainstream. Meanwhile, most 32-bit applications work fine under 64-bit Windows. If any of them don’t for some reason, you can reasonably expect the application developers to fix any issues, because a lot of people will be running 64-bit Windows.


Check If Your PC Supports 64-Bit Windows 7
If you have bought or upgraded your computer in the past couple of years, with an Intel Core 2 Duo or equivalent/higher processor, your PC is already equipped to run 64-bit Windows 7. If you want to make sure, you can do any of the following:

■If you are running 32-bit Windows Vista, go to Control Panel > System and Maintenance > Performance Information and Tools. Click View and print details. In the System section, you can see whether your PC is 64-bit capable.
■You can use the Windows 7 Upgrade Advisor to find out if your system can run 64-bit Windows 7.
■Check the Windows 7 Compatibility Center to see if your devices have 64-bit drivers.


When You Should Use 32-bit
There are some situations in which you are better off using 32-bit Windows 7:

■If you use only 2GB of RAM, and do not plan to upgrade anytime soon. To really take advantage of 64-bit Windows 7, you need minimum 4GB RAM.
■You have legacy devices like scanners and printers that do not have 64-bit device drivers. 32-bit drivers are not supported under 64-bit Windows 7, so you should make sure all the devices you need to use are compatible with 64-bit.
■You run old 16-bit applications that were developed for Windows 3.1 or DOS. These won’t run under 64-bit Windows.


http://www.makeuseof.com/tag/how-to-choose-between-32-bit-64-bit-windows-7-operating-systems/

Thursday, February 24, 2011

convert and cast functions for SQL Date Time

-- 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

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) -- 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

-- 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

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
*/

-- 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

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

-- 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'

----------

-- 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

GO

-- 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

GO

-- 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

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

-- 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

-- 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

GO

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

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

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

-- Extract string date from text with PATINDEX pattern matching

-- Apply sql server string to date conversion

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

USE tempdb;

go

CREATE TABLE InsiderTransaction (

InsiderTransactionID int identity primary key,

TradeDate datetime,

TradeMsg varchar(256),

ModifiedDate datetime default (getdate()))

-- Populate table with dummy data

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Sell 5,648 8.49')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Option Execute 5,648 2.15')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Hammer, Bruce D. CSO 07-31-08 Buy 5,000 8.05')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC Lennot, Mark B. Director 08-31-07 Buy 1,500 9.97')

INSERT InsiderTransaction (TradeMsg) VALUES(

'INSIDER TRAN QABC O''Neal, Linda COO 08-01-08 Sell 5,000 6.50')

-- Extract dates from stock trade message text

-- Pattern match for MM-DD-YY using the PATINDEX string function

SELECT TradeDate=substring(TradeMsg,

patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8)

FROM InsiderTransaction

WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0

/* Partial results

TradeDate

09-02-08

08-25-08

08-20-08 */

-- Update table with extracted date

-- Convert string date to datetime

UPDATE InsiderTransaction

SET TradeDate = convert(datetime, substring(TradeMsg,

patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8))

WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0

SELECT * FROM InsiderTransaction ORDER BY TradeDate desc

/* Partial results

InsiderTransactionID TradeDate TradeMsg ModifiedDate

1 2008-09-02 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10 2008-12-22 20:25:19.263

2 2008-08-25 00:00:00.000 INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70 2008-12-22 20:25:19.263 */

-- Cleanup task

DROP TABLE InsiderTransaction

/************

VALID DATE RANGES FOR DATE / DATETIME DATA TYPES

DATE (3 bytes) date range:

January 1, 1 A.D. through December 31, 9999 A.D.

SMALLDATETIME (4 bytes) date range:

January 1, 1900 through June 6, 2079

DATETIME (8 bytes) date range:

January 1, 1753 through December 31, 9999

DATETIME2 (6-8 bytes) date range:

January 1, 1 A.D. through December 31, 9999 A.D.

-- The statement below will give a date range error

SELECT CONVERT(smalldatetime, '2110-01-01')

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a smalldatetime data type

resulted in an out-of-range value. */

************/

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

-- SQL CONVERT DATE/DATETIME script applying table variable

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

-- SQL Server convert date

-- Datetime column is converted into date only string column

DECLARE @sqlConvertDate TABLE ( DatetimeColumn datetime,

DateColumn char(10));

INSERT @sqlConvertDate (DatetimeColumn) SELECT GETDATE()

UPDATE @sqlConvertDate

SET DateColumn = CONVERT(char(10), DatetimeColumn, 111)

SELECT * FROM @sqlConvertDate

-- SQL Server convert datetime - String date column converted into datetime column

UPDATE @sqlConvertDate

SET DatetimeColumn = CONVERT(Datetime, DateColumn, 111)

SELECT * FROM @sqlConvertDate

-- Equivalent formulation - SQL Server cast datetime

UPDATE @sqlConvertDate

SET DatetimeColumn = CAST(DateColumn AS datetime)

SELECT * FROM @sqlConvertDate

/* First results

DatetimeColumn DateColumn

2012-12-25 15:54:10.363 2012/12/25 */

/* Second results:

DatetimeColumn DateColumn

2012-12-25 00:00:00.000 2012/12/25 */

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

-- SQL date sequence generation with DATEADD & table variable

-- SQL Server cast datetime to string - SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

WHILE ( @i < 500)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT DateSequence = CAST(DATEADD(day, Sequence,getdate()) AS varchar)

FROM @Sequence

/* Partial results:

DateSequence

Dec 31 2008 3:02AM

Jan 1 2009 3:02AM

Jan 2 2009 3:02AM

Jan 3 2009 3:02AM

Jan 4 2009 3:02AM */

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

-- SQL Last Week calculations

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

-- SQL last Friday - Implied string to datetime conversions in DATEADD & DATEDIFF

DECLARE @BaseFriday CHAR(8), @LastFriday datetime, @LastMonday datetime

SET @BaseFriday = '19000105'

SELECT @LastFriday = DATEADD(dd,

(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7, @BaseFriday)

SELECT [Last Friday] = @LastFriday

-- Result: 2008-12-26 00:00:00.000

-- SQL last Monday (last week's Monday)

SELECT @LastMonday=DATEADD(dd,

(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7 - 4, @BaseFriday)

SELECT [Last Monday]= @LastMonday

-- Result: 2008-12-22 00:00:00.000

-- SQL last week - SUN - SAT

SELECT [Last Week] = CONVERT(varchar,dateadd(day, -1, @LastMonday), 101)+ ' - ' +

CONVERT(varchar,dateadd(day, 1, @LastFriday), 101)

-- Result: 12/21/2008 - 12/27/2008

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

-- Specific day calculations

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

-- First day of current month

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

-- 15th day of current month

SELECT dateadd(day,14,dateadd(month,datediff(month,0,getdate()),0))

-- First Monday of current month

SELECT dateadd(day, (9-datepart(weekday,

dateadd(month, datediff(month, 0, getdate()), 0)))%7,

dateadd(month, datediff(month, 0, getdate()), 0))

-- Next Monday calculation from the reference date which was a Monday

DECLARE @Now datetime = GETDATE();

DECLARE @NextMonday datetime = DATEADD(dd, ((DATEDIFF(dd, '19000101', @Now)

/ 7) * 7) + 7, '19000101');

SELECT [Now]=@Now, [Next Monday]=@NextMonday

-- Last Friday of current month

SELECT dateadd(day, -7+(6-datepart(weekday,

dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,

dateadd(month, datediff(month, 0, getdate())+1, 0))

-- First day of next month

SELECT dateadd(month, datediff(month, 0, getdate())+1, 0)

-- 15th of next month

SELECT dateadd(day,14, dateadd(month, datediff(month, 0, getdate())+1, 0))

-- First Monday of next month

SELECT dateadd(day, (9-datepart(weekday,

dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,

dateadd(month, datediff(month, 0, getdate())+1, 0))

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

-- SQL Last Date calculations

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

-- Last day of prior month - Last day of previous month

SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)),101)

-- 01/31/2019

-- Last day of current month

SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0)),101)

-- 02/28/2019

-- Last day of prior quarter - Last day of previous quarter

SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate() ), 0)),101)

-- 12/31/2018

-- Last day of current quarter - Last day of current quarter

SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate())+1, 0)),101)

-- 03/31/2019

-- Last day of prior year - Last day of previous year

SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)),101)

-- 12/31/2018

-- Last day of current year

SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)),101)

-- 12/31/2019

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

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

-- SQL Server dateformat and language setting

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

-- T-SQL set language - String to date conversion

SET LANGUAGE us_english

SELECT CAST('2018-03-15' AS datetime)

-- 2018-03-15 00:00:00.000

SET LANGUAGE british

SELECT CAST('2018-03-15' AS datetime)

/* Msg 242, Level 16, State 3, Line 2

The conversion of a varchar data type to a datetime data type resulted in

an out-of-range value.

*/

SELECT CAST('2018-15-03' AS datetime)

-- 2018-03-15 00:00:00.000

SET LANGUAGE us_english

-- SQL dateformat with language dependency

SELECT name, alias, dateformat

FROM sys.syslanguages

WHERE langid in (0,1,2,4,5,6,7,10,11,13,23,31)

GO

/*

name alias dateformat

us_english English mdy

Deutsch German dmy

Français French dmy

Dansk Danish dmy

Español Spanish dmy

Italiano Italian dmy

Nederlands Dutch dmy

Suomi Finnish dmy

Svenska Swedish ymd

magyar Hungarian ymd

British British English dmy

Arabic Arabic dmy */

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