.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

SQL Server Date Formats

Posted By: Amit Mehra     Posted Date: April 22, 2010    Points: 2   Category :Sql Server
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.

View Complete Post

More Related Resource Links

Essential SQL Server Date, Time and DateTime Functions

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.

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.

Saving JQuery date back to SQL Server


Hi..my web app uses lots of asp:textboxes and I can save this data back to SQL Server easily using a stored procedure..

however my JQuery datepicker uses an input textbox, how do I save the date entered into this control.

<input id="datepicker"type="text" /> back to server and can I use the same stored procedure that I use for the other data?



after restoring content database getting error (HTTP/1.1 200 OK Server: Microsoft-IIS/7.5 Date: Tue,

Hi All, Problem i have taken backup of Old site content database procedure see below 1) first i changed the database to read only mode (Opened SQL Server2005-->selected DB--> properties--> Option-->Database Read-Only=True) 2) taken backup of database from the task--Backup(new.bak) 3) Copied the taken backup to new server and restored (Opened SQL Server2005 --> Right Click on Database Folder--> Restore Dataase) In To database: ihave given DB name From Device: i have selected database from the location where i have saved. click ok.  It has restored successfully 4) I have created a new webapplication and removed its content database from central admin. 5) with the help of stsadm command i restored the above db to this webapplication. then iam getting below error: HTTP/1.1 404 Connection: close Date: Tue, 14 Sep 2010 06:20:08 GMT Server: Microsoft-IIS/6.0 X-Powered-By: ASP.NET MicrosoftSharePointTeamServices: i have refered the below url for restoring http://metahat.blogspot.com/2008/11/backup-restoring-sharepoint-2007-site.html Please help. Thanks in advance.

Convert Remedy ARSystem Date in SQL Server to short date format

Hi: Does anyone know how to convert Remedy ARSystem Date in SQL Server to short date format compensating for EST and Daylight savings. Thank you in advance!

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

Multiple date formats in pipeline......

I have a large record set that contains several date columns.  The date formats are MMYY,  YYMMDD.  and YMMDD (integer format).  My requirements require that the above mentioned formats of these dates need to formatted to that of YYYYMMDD.  In addition it also a requirement that the MMYY date format be either the first of the month of last day of the month.  What would be an efficient method of processing?  Multiple data flows to handle each format?  One data flow utilizing a lookup to a calendar table?  How would I handle if needed to add other date columns into the record set at a later time.
SQL - new guy

regex for diffrent date time formats


Every date time format can  be represented by a regular expression 

how can i get the a regular expression for a perticulat format?

using .net API

Converting SQL Server Date from mm/dd/yyyy to dd/mm/yyyy


I have a form on my website which inserts information into a table stored in sql server 2008... when I try to submit the form and I have entered a date like "08/10/2010" it works perfecting... but when trying to enter "24/10/2010" it doesn't work as the default format in SQL server is "mm/dd/yyyy" - so throws up an error saying:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

Also when I enter the date "08/10/2010" then submit the form, when retreiving that date on another page it displays as "10/08/2010"?!?!?!

Any help is much appreciated :)

Sql Server and Access Date Calculation


Hi Everybody,


We are working on same Sql Server and Access database tables. All columns and values same. But When I run query on sql and access, access and sql return different values to me.

Access Sql Query:

Int(Now())-[myDateColumn])/30 = 23.986760875771


Sql Server Query

(CONVERT(INT,GETDATE()) - CONVERT(INT,[myDateColumn])) / 30.0 = 24.033333


Why different values??


Thank you.



how set the default value for date inSQL Server?



i need to set a default value for a column of date

i need when the column dont get a date automatic set 1/1/1900 for it.

can you please help me?

match date in sql server


I have something like this




.intBarcode as [barcode],


.intSequence as [sequence],


SharePoint Portal Server 2003 - email alert based on due date


Hello All,

Hoping someone can help us here.

Note:Workflow/3rd party solutions not an option-

We would like to send an email alert when a list items due date is eqaul to "today".

We are trying to manage some certificates of compliance by flagging the "due date of the particular certificate (list item)" and sending an alert email when that occurs.

Is this feasible in any manner without workflow?

Is it possible to send an alert based on a calculated field when that calculated data changes? .

Any direction would be appreciated.

Thank you

Mike Harvey
Software Solutions Architect


Date picker shows "Server Error"

Out of the blue, the standard date picker in default SP forms (not InfoPath) show a server error instead of the current date in a month view. Anyone seen this before?

sql server 2008 date


can I somehow enter a default date of 00000000 into a column of type date even though this is outside the allowed date range?

Localization of date formats in .NET\Windows


We are in the process of regionalizing an application, in this effort we want dates to conform to a given regions format sub BG "d/MM/yyyy". We have implemented this using resources\culturinfo in our application. We use the resource to hold the format we need to retrieve for a given region. The backend we're using is SQL server 2008. One of the concerns we had was, even though we're displaying dates in a given format such as "d/MM/yyyy", our system still writes that data from the controls we're using in the application as "MM/dd/yyyy", this is something we want to happen, and all is great. Also literature states that SQL server logins determine the format of dates when connecting to SQL server for data updates and inserts. Since our login will have its language set to US-English, we appear to be fine. Testing of this model on a system by changing the regional settings does not affect the model in place.  My question is, with this type of model in place where the date format is driven by embedded resource (resx) files and the SQL login using US-English, are there any other machine environment setting\variables (OS or .NET framework) to consider that can cause grief? I can provide any information is needed to make my questio

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