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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

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

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
 

Hi,

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.

http://msdn.microsoft.com/en-us/library/ms145276%28v=sql.90%29.aspx

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

Appreciate help.

 

<


View Complete Post


The Time Zone
DST Start DST End Offset for UTC
EST 3/13/11 4:00 11/6/11 3:00 240

More Related Resource Links

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...

Biswa


bplthebest

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?

Thanks


The Free Barcode Creator produces barcode images one at a time, and allows easy saving to a file or

  
The Free Barcode Creator produces barcode images one at a time, and allows easy saving to a file or pasting into an application. 2D versions are also available:

lookup table query

  

we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?



Showing available time slot in table format

  

I'm currently developing a system for booking of discussion room in a college. This system will allow the staff to help the students to make booking in advanced as well as for instant walk in usage.

I've got a table to keep track of bookings, another for walk in and one for time slot.

For the time slot table, it includes start time slot and end time slot, which is 30 minutes interval for each.

What i'm currently facing is that the system could not display the correct available time slot. I think it's the formula problem which I still can't get it solved.

Example:

If the booking time is 8.30am - 9.00am, I have no problem showing the 1st slot as N/A.

But if the time is 8.45am - 9.15am, the system only update the 1st slot as N/A while the 2nd slot still remain as AVAILABLE which is wrong. Because since the time ends at 9.15, I would like the slot 9.00am - 9.30am to be N/A as well.

This would be my code:

for (int x = 0; x < countTimeSlot; x++)
{
     if (int.Parse(arrEndTimeSlot[x].ToString()) > intBookedStartTime && int.Parse(

How to insert the Current date and time in to SQL Table..

  
Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

Count numbers of days (no time table in datasource)

  
I read the following regarding adding a measure for counting the number of days: The trick is to create new measure group, which will have only one dimension - Time, and a measure with Aggregation Type 'Count' bound to the Day attribute of Time dimension (or any other attribute if we need to count something different from days). The most important setting on this new measure group is to leave IgnoreUnrelatedDimensions=true - this will allow to use this measure across any other dimension in the cube. Howerver, this solution assumes that one has a time dimension based on a table in the datasource. I have a Time dimension that created was by the BIDS wizard that is not in my source database but appearantly invisibly hidden on the AS server without direct access to it. (If I try to add a new measure group (as needed according to the post above) then I can only choose from tables that have nothing to do with time). Can somebody explain how I can do this without a specific timetable in my datasource? Do I need to add a timetable just for this purpose? Can it be stand-alone or do I need to add a relationship with all current tables that have date-columns in them? (I am a finance manager that is building a sales cube based on Navision 3.6, which is already working quite nicely, but still very much a newby)

lookup: unable to allocate memory for hash table

  
Hi, I have been getting an error from a lookup component when I run it from the job agent. However, it works just fine from debug mode. the specific error is 0xC0208247: unable to allocate memory for hash table. I get the same error when I run this not in full-cach but in partial and no cache. Also, the default butter max row and buffer size in package are set to default. Does anyone have any idea.   Jon

epased time for few SQL queries is 0 in sys.dm_exec_query_stats table?

  

Hi,

I get the elapsed time from sys.dm_exec_query_stats table for each SQL query that I run in MSSQL. I see that the elapsed time for some of the SQL queries are shown as 0 while oracle performance statistics data show some time for the same query.

I believe MSSQL can't take 0 microseconds to execute a query. Should I need to force MSSQL to monitor the lesser time taking queries?

I also used "SET STATISTICS TIME ON", I get 0 elapsed time from this too for those queries which had 0 elapsed time from sys.dm_exec_query_stats.

Any help will be helpful


15 minute incrementable UTC time inserts in the table

  

What is the most efficient way of creating a 15 minute increment table. This table needs to continue to increment with time so its not a static table.

While loop? how will that affect the performance, if I have to add dates starting 2005 till today and then basically after that with every hour a value needs to be added.. How can I go about doing that?

 

Thanks

-Sarah 


TODATETIMEOFFSET fails only when reading smallint offset value out of a table

  

SQL Server 2008 R2 Developer

I'm getting a completely bizarre failure with the below repro code.  It ONLY fails in scenario #2, where I get "The timezone provided to builtin function todatetimeoffset is invalid."  I can't for the life of me understand what the difference is between scenario #2 and #3 from the function's perspective.  Once the value is assigned to the local SMALLINT variable, why does it matter where the value came from?

DECLARE	@OrgTimezoneOffset1 INT,
	@OrgTimezoneOffset2 SMALLINT,
	@OrgTimezoneOffset3 SMALLINT

CREATE TABLE dbo.Timezone (
	TimeZoneName VARCHAR(100),
	TimeZoneOffset SMALLINT
)

INSERT INTO dbo.Timezone
SELECT	'(UTC-07:00) Arizona',
	-420

SELECT @OrgTimezoneOffset1 = tz.TimezoneOffset
FROM dbo.Timezone tz
WHERE tz.TimezoneName = '(UTC-07:00) Arizona'

SELECT @OrgTimezoneOffset2 = tz.TimezoneOffset
FROM dbo.Timezone tz
WHERE tz.TimezoneName = '(UTC-07:00) Arizona'

SELECT @OrgTimezoneOffset3 = -420

SELECT @OrgTimezoneOffset1, @OrgTimezoneOffset2, @OrgTimezoneOffset3

--Scenario #1: INT variable, assigned from table query - works
SELECT TODATETIMEOFFSET(GETUTCDATE(), @OrgTimezoneOffset1)

--Scenario #2: SMALLINT variable, assigned from table query - fails
BEGIN TRY
	SELECT TODATETIMEOFFSET(GETUTCDATE(), @OrgTimezoneOffset2)
END TRY
BEGIN CATCH
	SELECT ERROR_MESSAGE()
END CA

Insert data in parent and child table at the same time

  

I have two (parent-child) tables that are bound by a primary key (PK)/foreign key (FK) relation.each PK is identity (idStudent and idBac)

to insert data, i'm using an little application in c# i programmed myself where i insert these columns for the Student table: F_Name, L_Name, Age, Mobile, Adresse, City ,and  ( i don't know how to handle the idBac wich is foreign key of the other table wich still not inserted yet)

for the table Bac, i insert section, school, and city.

what i want is when i click on "OK" button, the idBac ,wich is a foreign key of the other table (BAC), receives the same idBac given the moment i inserted the BAC informations too ...

i don't have any problem wich ado.net ...i just want the right query/procedure.

thanks in advance and sorry for my english, it's not my mother tongue.


web service for Daylight Saving

  

Hi All,

I am looking for a web service that accepts a (date, Latitude Degrees, and Longitude Degrees) for a certain City,and returns if Daylight Saving is on for that city in the given date or not.

any help please?

thanks,


SSIS 2008 - Fuzzy lookup warming index takes too much time

  

Hello All,

We are using Fuzzy lookup transformation in our SSIS package.

We have selected option to store and maintain index for first time. And for subsequent executions, we use option to use existing index. So it will not create new index each time. We are doing fuzzy lookup on single source table with single column. The source table contains more than 2.5 million rows.

So while executing the package, although its not taking time to build complete index. But still it takes considerable time and uses large memory whenever we execute it.

We have also set the WarmCache option to true. Although all data is loaded into memory and removed after execution.

Is there any other way so we can keep the data in memory, so for subsequent execution, it gets result directly from memory?

Please let me know if you have any suggestion? Its urgent.

 

Thanks in advance.



Darshan Vyas

Adding rows in table at run time?

  

Hi,

    I have a table which has few rows.

    A row contain few textboxes, one file upload field and a dropdown list in its cells.

    I want to make it a dynamic table that is i should be able to add extra rows in table at runtime, but I dont know how to do it.

    Please Help.


getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


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