.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Essential SQL Server Date, Time and DateTime Functions

Posted By: Venkat     Posted Date: April 13, 2010    Points: 2   Category :Sql Server
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

View Complete Post

More Related Resource Links

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Date/Time Conversions Using SQL Server

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

Date time of a document is not synchronized with the server




I upload a new document to a document library on SharePoint 2007. The time on my computer and on SharePoint server was correct, despite this the modified time of the document was set to an hour forward.

What can I do to fix the problem.


Thanks in advanced


How to store only date and only time into sql server 2008 from C# asp.net 3.5


Hi 2 all,

can anyone help me how to store only date into sql server 2008 from C# asp.net 3.5.

I want to store only date(like 26/05/1986) and only time (like 04:45:40 AM) into sql server 2008 .

I am using C# asp.net 3.5 as front end.But i am not getting how to convert datetime into only date and only time from front end application.

It should not be as a string from front end.it should be as a date type / time type.

I can not see only date or only time data type in VS 2008.

so please help me how to store only date or only time into sql server 2008 having only date or only time as a data type.

Thanks and Regards,

Arvind Pathak

getting time and date from datetime field in javascript


Hello guys, I need to get hours from the difference between two datetime fields. I wrote the javascript which will get the date from datetime field.

But its not getting the correct time that entered on date time field. Its defaultly getting the current time on my system.

So do you guys have any idea. How to get time from date time fields. and how calculate hours between two datetime fields using javascript.

want to display 24 hours time and date with datatype Datetime (Not String) in SSRS


Hi All,

I want to display 24 hours time and date in SSRS & for that I am using Format(now,"MM/dd/yyyy HH:mm:ss") but I am using this expression in Parameter, where I need to define datatype of that parameter as DateTime, not string. For Expression Format(now,"MM/dd/yyyy HH:mm:ss"), If I use DateTime datatype, It is showing error but working with String datatype.

For example:

I have parameter To Date  and value of this parameter should be 10/11/2010 13:09:16 (To Date: 10/11/2010 23:09:16) , not in AP or PM format and datatype of this parameter, want to keep DateTime that I can select value from Calender.

Please suggest me how I can achieve this?. I don't want to do it in stored Procedure side, want to do it in report side.

Thanks Shiven:)

Seperate DateTime into Date column and Time column in query


This is probably a simple question but here it goes.

First off let me state that the naming of these columns is not by my choice nor can I change them.  We purchased some software that automatically does the naming conventions for it's fields (Sample1).

I have a table with 2 columns in it:

Sample1                      Time is in GMT Time

Time           DateTime, PK        

LMP             Float

Table 2:

ABC_Price                   (Day, Hour) is in EST Time

Day               SmallDateTime   PK

Hour              TinyInt       PK

MW                Decimal(7,3)

Price              Money


What I need to do is split the Ti

Unable to convert MySQL date/time value to System.DateTime



I get the following error when i call my method from the class any ideas?

Unable to convert MySQL date/time value to System.DateTime

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime

Source Error:

Line 324:        string strqry = "Select * FROM tblclients";
Line 325:
Line 326: DataTable returnDatatbl = conClass.queryDB(strqry);
Line 327:
Line 328:

Data validation on a datetime column - or remove date part and only keep time part of the datetime2


Windows Server 2003, SharePoint Designer 2007, Windows SharePoint Services 3.0

I have a calendar list with start date(datetime1) and end date (datetime2).  Each is the default column created by the calendar list in SharePoint.  They both display a date and a time to choose from.

QUESTION:  Is there a way to remove the date section of the end date in a custom list form so that the user can only pick an end TIME?  Or, is there a way to force the end date (datetime2) to HAVE TO BE the same DATE as datetime1, only allowing for a different time?

Thank you!

Hijri Date Time store in SQL server DB



I have a problem and I searched a lot without any benefits

How can I store hijri date in datetime field in SQL server 2005

Is SQL server db support this?

And how can I deal with this problem and generate crystal report with hijri dates.


: )


How to receive current date and time from exchange server when ISA/TMG is in between ?


I am reading Exchange resource mailbox using .Net CF C#. For WebDAV communicatoin, I have used a third party API and It's working fine. For EWS I have developed my own library. But I could  not find the way for receiving current time form exchange server.

To achieve this I have used following code:



private DateTime getCurrentServerTime(

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.


Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

User Defined Functions in Microsoft SQL Server

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are often asked by SQL Server developers.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend