Introduction
Date and time values are highly used in database applications. They involve sales, time sheets, taxes, overtime work, etc. Based on this usefulness, their operations are supported by various libraries you will be using when developing your application. Without being the most elaborate on this issue, Transact-SQL provides its own level of support for date and time values.
Before using a date or a time value in a calculation, remember that you must first get it one way or another. You can define a date or a time constant in your application. An example would be '1992/10/28'. You can declare a DateTime or a SmallDateTime variable and initialize it as you see fit. You may get a date or a time from another function. As the last alternative, you may get a date or time from another application or from a user. Once you have an appropriate date, you can use it.
The Current System Date and/or Time
One of the ways you can assist the user with date and time is to get the current date or the current time. For example, if you create a time sheet, when the user starts using it, it would be convenient to fill part of the time sheet with such predictable values.
To get the current date and the current time of the computer that a user is using, you can use the GETDATE() function of Transact-SQL. Its syntax is:
GETDATE()
This function simply returns the current date and time of the operating system.
One of the primary operations you may want to perform on a date or a time value would consist of adding a value to it. To support this operation, Transact-SQL provides the DATEADD() function. Its syntax is:
DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)
The third argument to this function is the value of a date or a time on which the operation will be performed. It can be a constant value in the form of 'year/month/day' for a date or 'hour:minutes AM/PM' for a time.
The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06.
When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:
- If you want to add a number of years to a date, specify the TypeOfValue as Year or yy, or yyyy (remember that SQL is case-insensitive). Here is an example:
DECLARE @Anniversary As DateTime; SET @Anniversary = '2002/10/02'; SELECT DATEADD(yy, 4, @Anniversary) AS Anniversary; GO
- If you want to add a number of quarters of a year to a date, specify the TypeOfValue as Quarter or d, or qq. Here is an example:
DECLARE @NextVacation As DateTime; SET @NextVacation = '2002/10/02'; SELECT DATEADD(Quarter, 2, @NextVacation) AS [Next Vacation]; GO
- If you want to add a number of months to a date, specify the TypeOfValue as Month or m, or mm. The following example adds 5 months to its date:
DECLARE @SchoolStart As DateTime; SET @SchoolStart = '2004/05/12'; SELECT DATEADD(m, 5, @SchoolStart) AS [School Start]; GO
In the same way, you can add values as follows:
|
Date/Time Subtraction
Another regular operation performed on a date or a time value consists of getting the number of units that has elapsed in the range of two dates or two time values. To support this operation, Transact-SQL provides the DATEDIFF() function. Its syntax is:
DATEDIFF(TypeOfValue, StartDate, EndDate)
This function takes three arguments. The second argument is the starting date or the starting time of the range to be considered. The third argument is the end or last date or time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function:
Here is an example that calculates the number of years that an employees has been with the company:
DECLARE @DateHired As DateTime,;
@CurrentDate As DateTime;
SET @DateHired = '1996/10/04';
SET @CurrentDate = GETDATE();
SELECT DATEDIFF(year, @DateHired, @CurrentDate)
AS [Current Experience];
GO
This would produce:
What is the difference between .Net 2.0 and .Net 3.5
Recently I was asked by a colleague to investigate and report on the differences between .Net 2.0 and .Net 3.5 so this seemed like an excellent opportunity to explore this subject for the community as a whole. While most consultants working within the CRM community don’t really have a tremendous amount to do with .Net programming, less than say a professional programmer, it is still useful to understand the differences between the frameworks and the new features that have been added to the latest release .Net 3.5. So, for you edification, here is an article summarising the differences.
Some History (.Net 1.1)
.Net 1.1 and .Net 2.0 frameworks focused on allowing different languages to communicate with a common set of libraries translated through the Common Language Runtime (CLR). Essentially, the CLR provides a managed and language agnostic environment for executing applications designed for the .Net Framework. The managed runtime environment provides a number of services to the code, these services include compilation, code safety verification, code execution, automatic memory management and other system services. The applications designed to run under the CLR are known as managed applications because they enjoy the benefit o f services offered by the managed execution environment provided by the CLR.
.Net Framework 2.0
As .Net Framework 1.1 focused on consolidating and improving the CLR, .Net Framework 2.0 brought some new additions to the table. Such as :
· Virtual Machine : Support for generics
· Class Library : Generic Classes, including nullable types
· C# Features : Generics, partial classes, anonymous methods etc
Further, ASP.NET 2.0 introduced a number of new data Web controls, including the GridView, DetailsView, and FormView controls. The GridView displays a list of records in a boxy, grid-like table. The DetailsView and FormView each display one record at a time, the DetailsView in a grid-like output and the FormView in a more fluid layout using templates. The ListView Control was an update to the old DataList and Repeater controls, displaying mulitple records and providing functionality like the GridView, but allowing for a more flexible layout through the use of templates. The DataPager control operated as a sort of free-standing paging interface.
The new API gave a fine grain control on the behaviour of the runtime with regards to multithreading, memory allocation, assembly loading and more. Full 64-bit support for both the x64 and the IA64 hardware platforms was introduced along with the new personalisation features for ASP.NET, such as support for themes, skins and webparts as well as a .NET Micro Framework.
.Net Framework 3.0
The .Net Framework 3.0 was not a mere improvement to .Net 2.0, it in fact introduced 4 completely new foundation technologies although it continued to use the .Net 2.0 framework as a basis. Also called WinFX, it included a new set of managed code APIs that are an integral part of Windows Vista and Windows Server 2008 operating systems.
· Windows Presentation Foundation (WPF) AKA Avalon
provided the foundation for building applications and high fidelity experiences in Windows Vista, blending together application UI, documents, and media content. The functionality extends to the support for Tablet and other forms of input, a more modern imaging and printing pipeline, accessibility and UI automation infrastructure, data driven UI and visualization, as well as the integration points for weaving the application experience into the Windows shell.
· Windows Communication Foundation (WCF) AKA Indigo
is the Microsoft messaging platform for building service-oriented applications. Clients communicate with services by exchanging messages that are serialized on the wire, and de-serialized into common language runtime (CLR) types at each end. In the simplest scenario, client and service developers only work with objects, and all the serialization magic happens somewhere down below in the plumbing. Windows Communication Foundation (WCF) provides this plumbing and is similar in many ways to Web Services.
· Windows Workflow Foundation (WWF)
Windows Workflow Foundation is a Microsoft technology for defining, executing, and managing workflows
· Windows CardSpace (WCS) AKA InfoCard
CardSpace allows users to create personal (also known as self-issued) Information Cards, which can contain one or more of 14 fields of telephone book-quality identity information (that is to say, labelled and categorized information, such as full name, address, etc., though most fields are optional). Other transactions may require a managed InfoCard; these are issued by a third party identity provider that makes the claims on the person's behalf, such as a bank, employer, or a government agency. In short, it is a software component which securely stores a person's digital identities and provides a unified interface for choosing the identity for a particular transaction, such as logging in to a website
Many developers do not generally view this as a real framework in its own right. The real comparison is therefore between .Net 2.0 and .Net 3.5
.Net Framework 3.5
.Net Framework 3.5 builds incrementally in the new features added in .Net Framework 3.0. For example, feature sets in WWF, WCF, WPF and WCS. In addition, .Net Framework 3.5 contains a number of new features in several technology areas which have been added as new assemblies to avoid breaking changes. They include the following :
Deep integration of Language Integrated Query (LINQ) and data awareness. This new feature lets the developer write code written in LINQ enabled languages to filter, enumerate and create projections of several types of SQL data, collections, XML and DataSets by using the same syntax.
It should also be noted that .Net Framework 3.5 has added support for ASP.Net AJAX and Silverlight. Further enhancements that can be considered are :
1) CLR enhancements : general
2) Compiler enhancements : New VB.Net 9.0 compiler and support for changes to C# 3.0 like expression trees, lambda methods, extension methods, static reference for anonymous types etc.
3) LINQ : LINQ to XML, LINQ to SQL, LINQ to Objects and LINQ to datasets. Along with functional programming, LINQ is an outlook change to programming in C#.
4) Performance Improvements : ADO.Net gets paging support as well as synchronisation from caches at local level and server datastores. Also performance improvements for multi-code CPU’s.
5) Networking Changes : Peer to peer networking stack, including managed PNRP revolver.
6) Windows Information APIs : new wrapper for WMI and Active Directory Services. WMI 3.0 gets a managed provider
7) ASP.Net : New implementation of Client Application services as well as 3 new ASP.Net controls. Also AJAX programming for ASP.Net is easier and performs better.
8) WCF : Now works with POX and JSON data
9) WPF : Newer plugin model for creating AddIns. Silverlight CLR is also part of the new .Net Framework 3.5
10) Misc : The C/C++ get a standard template library (STL) so that these languages can share .Net libraries. Javascript intellisense and Javascript debugging, a CSS properties windows and a Managed Styles tools window as well as a new System.CodeDom namespace.
Conclusion
We’ve come along way from Framework 1.1 to Framework 3.5. For me, the most exciting developments are with LINQ, Silverlight and AJAX. I can foresee that these will be very big in the future and will enable the developers of the future to create some truly interesting and useful CRM additions.