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

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

SQL DateTime and daylight savings time

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :ASP.Net

I am using Membership and note that all the dates set in the aspnet_Membership table are minus one hour.

My development platform is XP and my timezone is set to GMT + 0.00 with "automatically adjust clock for daylight saving changes" checked.

Currently we are in British Summer Time (joke!) which is GMT + 1 hour = BST.

If I set a DateTime field = NOW(), the DateTime field is set to BST.

When using the Login control, however, the DateTime set in any field, LastActivityDate, LastLockedDate or FailedPasswordAttemptWindowStart, is BST - 1 = GMT.

First question is, should I set dates to be in GMT all the time or BST when it applies?

If GMT all the time (which seems to be the preferred option?), how do I remove the daylight saving time when I set a DateTime field?

If BST when it applies, how do I add this to the dates set by the Login control?

Your help would be much appreciated (any codes snippets preferably in VB, please).

Thanking you in anticipation.



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.

SP Datetime parameter requires time input

I'm working on a Crystal report that contains 4 subreports.  Each subreport passes 2 date parameters to a stored procedure.  This works, but only if a value for time is also entered in the Crystal parameter prompt. How can I fix the stored procedure so these parameters will accept only a date as input?

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

Map the start and end datetime fields of an historical dimension to the time dimension and the fact



I have a dimension with some historical attributes and I use start and end datetime fields to save the outdated records. I assume that when the cube is processed, each record in the fact tables is mapped to the matching record in the dimension by using the combination of the key attribute with the start and end datetime fields. My question is how to configure the mechanism so that it will know which field is the datetime field and use it to do the comparisons with the start and end fields?


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

How to find the Eastern Time after Daylight Saving Time difference (GMT - 4) or (GMT - 5)


Hi All,

I am using SQL Server 2005.

I have a requirement to find the Eastern Time,

Which is (GMT - 4) during Daylight Saving Time(DST)

and (GMT - 5) during non DST.


Can anyone help to develop a logic without hard coding any date and time values, to find the Eastern Time(EST)?


Thanks in Advance...



BCS - Datetime field and time zones


I have an external list that has a datetime column. I get the value for this column from my SQL server database.

The value in the database is '2010-10-30 17:00:00.000', but when the value is displayed in my external list it is converted to '2010-10-30 18:00:00.000'. This is done because the time zone for my sharepoint site is set to UTC+1.

I do not want SharePoint to convert the datetime. I want the value in external list to be '2010-10-30 17:00:00.000' no matter what the time zone is set to on my site.

How can I do that?


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!

How to handle Daylight Saving Time issues in sql server Database


Hi We have users across the globe and have issues everytime the Daylight Savings change occur. Is there a way to handle this issue in sql server?


Time Part from datetime


Hello all 

I want to access time part from datetime in datetime format not in varchar 

I used convert(varchar(50),mydt,108) 

or in other word is it possible to return time part from sql datetime in system time formate (means if 24 hours or 12 hours format with am/pm)



UTC Offset Lookup Table for different Time Zones with Daylight Saving.



I am trying to populate a table for on SQL 2008 Server which should have start datetime and end datetime for daylight saving for various world time zones. So that I can lookup if a particular date is between startdate and enddate and get UTC offset.

Something like the following table. Checked this link but not sure if I can use it in anyway for the information I am looking for or need something completely different.


I not an SQL expert and finding it really difficult to get solution for this problem which I am facing.

Appreciate help.



Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

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.

The Time Zone
DST Start DST End Offset for UTC
EST 3/13/11 4:00 11/6/11 3:00 240
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