Essential SQL Server Date, Time and DateTime Functions

Standard Date, Time & TimeSpan Functions

function DateOnly(@DateTime DateTime)
— Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime

create function Date(@Year int, @Month int, @Day int)
— returns a datetime value for the specified year, month and day
— Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime

create function Time(@Hour int, @Minute int, @Second int)
— Returns a datetime value for the specified time at the “base” date (1/1/1900)
— Many thanks to MVJ for providing this formula (see comments).
returns datetime

create function TimeOnly(@DateTime DateTime)
— returns only the time portion of a DateTime, at the “base” date (1/1/1900)
returns datetime

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
— returns a dateTime value for the date and time specified.
returns datetime

create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
— returns a datetime the specified # of days/hours/minutes/seconds from the “base” date of 1/1/1900 (a “TimeSpan”)
returns datetime

create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
— returns the # of units specified in the TimeSpan.
— The Unit parameter can be: “d” = days, “h” = hours, “m” = minutes, “s” = seconds
returns int

For More Details: Essential SQL Server Date, Time and DateTime Functions

see also:


List of .NET Dependency Injection Containers (IOC)


    Inversion of Control Containers and the Dependency Injection pattern

Vai Scott Hanselman’s Computer Zen – List of .NET Dependency Injection Containers (IOC)

Scott on Writing

Again…Excellent Tutorial from Scott Mitchell

  • Creating the Membership Schema in SQL Server [VB | C#] – explores the Membership framework and its goals. Looks at configuring and setting up the SqlMembershipProvider, which stores user account information in a Microsoft SQL Server database.
  • Creating User Accounts [VB | C#] – examines creating user accounts using the CreateUserWizard control as well as using the Membership class’s CreateUser method.
  • Validating User Credentials Against the Membership User Store [VB | C#] – shows how to validate a user’s supplied credentials and log them on (and off) the site. Looks at using both the Login Web control and the Membership.ValidateUser method.
  • User-Based Authorization [VB | C#] – examines how to restrict access to pages or functionality within a page based on the logged in user.
  • Storing Additional User Information [VB | C#] – the Membership framework only stores a handful of user attributes, but oftentimes additional, application-specific user information needs to be tracked. This tutorial looks at how to accomplish this.

Via Scott on Writing

TopXML : How can WCF support multiple IIS Binding specified per site in WCF


IIS supports specifying multiple IIS bindings per site, which results in multiple base addresses per scheme. A WCF service hosted under a site allows binding to only one baseAddress per scheme.

So if you get an error like this “This collection already contains an address with scheme http wcf”, you may want to look at the following:

Solution in .Net Fx 3.0:Supporting Multiple IIS Bindings Per Site

Solution in .Net Fx3.5: BaseAddressPrefixFilters




        <add prefix=””/>




Via TopXML : How can WCF support multiple IIS Binding specified per site in WSCF/WCF

Programming the Web using WCF 3.5 – ‘Web HTTP Programming Model’

Publishing RSS and ATOM Feeds using WCF 3.5 Syndication Libraries:

Windows Communication Foundation with its 3.5 release provides several new and useful features including capability to publish and consume syndication feeds in a much easier and uniform way, right out of the box. This article focuses on using the WCF 3.5 libraries namely System.ServiceModel.Syndication namespace to create and publish an RSS and Atom feed from the same code base. System.ServiceModel.Syndication provides a framework to perform the syndication leg-work for us including serialization and deserialization of web feeds.


HTTP Programming with WCF and the .NET Framework 3.5:

WCF in the .NET Framework 3.5 includes an easy-to-use HTTP programming model, JavaScript Object Notation (JSON) messaging capabilities, and a new syndication API that makes it easy to create and consume syndicated content. With this feature set, WCF is now the service platform of choice for connecting services to Web clients, whether they are ASP.NET AJAX controls, SilverlightTMclients, or even browsers. These features also work in partial trust scenarios (like ASP.NET medium trust) so you can host WCF services in widely available hosting environments. To round it all out, there’s also new tooling integrated into Visual Studio®2008 that dramatically reduces the amount of time it takes to get a service up and running.

This means you can build a service that communicates within or across enterprise boundaries using SOAP and WS-*, and you can configure that same service to communicate externally using the protocols of the Web – JSON, RSS & ATOM. 

It starts with a level-setting discussion about some of the important architectural principles in HTTP and the Web, then moves to the new HTTP programming model in WCF, and, finally, to the new syndication API.

  • HTTP message basics
  • Adding information to URIs
  • WCF and HTTP
  • RSS and ATOM with WCF
  •  image


    Use LINQ and .NET 3.5 to Convert RSS to JSON using DataContractJsonSerializer and HttpHandler

    JavaScriptSerializer is marked as obsolete with a note to use the DataContractJsonSerializer instead.  Here is what a generic HTTP Handler would look like that mashes up these techniques using .NET 3.5.


    Creating a JSON Service with WCF 3.5, DataContractJsonSerializer and WebGetAttribute



    Create RSS and Atom Feeds with LINQ and WCF Syndication in .NET 3.5 within ASPX




    Also directly from MSDN – WCF 3.5 Feature List


    Web Programming Model

    Web Programming Model Samples

    WCF Syndication

    Syndication Samples

    LINQ to SQL vs LINQ to Entities


    When Do I Use LINQ to SQL?

    The primary scenario for using LINQ to SQL is when building applications with a rapid development cycle and a simple one-to-one object to relational mapping against the Microsoft SQL Server family of databases. In other words, when building an application whose object model is structured very similarly to the existing database structure, or when a database for the application does not yet exist and there is no predisposition against creating a database schema that mirrors the object model; you can use LINQ to SQL to map a subset of tables directly to classes, with the required columns from each table represented as properties on the corresponding class. Usually in these scenarios, the database has not and/or will not be heavily normalized.


    When do I use LINQ to Entities?

    The primary scenario targeted by LINQ to Entities is a flexible and more complex mapping scenario, often seen in the enterprise, where the application is accessing data stored in Microsoft SQL Server or other-third party databases.

    In other words, the database in these scenarios contains a physical data structure that could be significantly different from what you expect your object model to look like. Often in these scenarios, the database is not owned or controlled by the application developer(s), but rather owned by a DBA or other third party, possibly preventing application developers from making any changes to the database and requiring them to adapt quickly to database changes that they may not have been aware of.


    Via Introducing LINQ to Relational Data

    Building a ToJSON() Extension Method using .NET 3.5

    Here…Serialize a List<Person> into a string of JSON format


    Here is the Extension method to provide the JSON serialization on the String Type.


    Note: In addition to the JavaScriptSerializer class, .NET 3.5 also now includes a new System.Runtime.Serialization.DataContractJsonSerializer class that you can use for JSON serialization/deserialization

    Via Tip/Trick: Building a ToJSON() Extension Method using .NET 3.5 – ScottGu’s Blog