In MS SQL 2000, I would like to obtain the timezone offset from UTC for
a given date. For today's date, I can do
DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
date may not be the same as today because some countries go in Daylight
Saving mode. Can you suggest a way to obtain the timezone offset for
any given date?
Thanks
Yash
Hi,
I have a requirement for a function which would convert a given date with a present timezone to a new timezone.
For eg : the function would be called lets say TZ
then I can issue the following select statement to get the new timezone
select getdate(), TZ(getdate(), PST, EST)
where PST is the current timezone and EST is the new timezone.
I know that ORACLE has a built in function to do this. Am just wondering if theres some function that I can use to accomplish the task.
Thanks
-soumil
Hello,
I am struggling with one algo i.e. I want to retrieve the date according to time zone
I have table called TIMEZONe in which i have the information about the hours difference (+12 to -12) from GMT...
now my scenario is that I want to get the date, using information from timezone area table (time difference from GMT), of any particluar time zone
can any one tell me, how will I able to achieve this task.
for example:
it 4:00 AM in the mornign in GMT TIME and date is 11-08-2006 but in US it 11:00 PM and date is 10-08-2006
Note: Server date is set to GMT (System date)
regards,
Anas
Can i able to find what time zone that the os has been set,
using some sql function
or is it better to use an sql script
like calculating the difference between GetUtcDate() and GetDate()
Hello,
I am struggling with one algo i.e. I want to retrieve the date according to time zone
I have table called TIMEZONe in which i have the information about the hours difference (+12 to -12) from GMT...
now my scenario is that I want to get the date, using information from timezone area table (time difference from GMT), of any particluar time zone
can any one tell me, how will I able to achieve this task.
for example:
it 4:00 AM in the mornign in GMT TIME and date is 11-08-2006 but in US it 11:00 PM and date is 10-08-2006
Note: Server date is set to GMT (System date)
regards,
Anas
Does the SQL Server equivalent of the ANSI SQL data type DATETIME (TIMESTAMP, if I'm not mistaken) have the notion of time zone? In a project, I store a complex timestamp, composed of a DATETIME field and a FLOAT type, which gives me a sub-microsecond resolution for thousands of years (all right, it's a bit of an overkill but not by much). I use both plain SQL commands and ADO.Net to store the data. It wasn't until I hit a violation of a constraint that I discovered that, at least the way I am doing it, SQL has no concept of time zones: when I wrote a value executing a SQL INSERT (and providing the DATETIME as a string, like this: '8/23/2006 12:43:09 PM') and then when I tried to store the same thing using the ADO.Net SqlAdapter->Update(), it said it was not the same value! Well, it turns out my object was set to be local time, while when parsing it off of a string the kind is "unspecified". The funny thing is that it appears as though just before writing to the SQL Server, the ADO.Net methods do a conversion to local but it ultimately seems to be keeping it as UTC because only then my DateTime values would be different. I can live with that but if I'm missing something and I have the ability to store the time zone as well, I'd like to do that.
Kamen
In C#, I can convert UTC time to a specific timezone using:
TimeZoneInfo localTimeZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
DateTime UTCTime = TimeZoneInfo.ConvertTimeToUtc(localTime, localTimeZone);
Is there any equivalent function in T-SQL (SQL Server 2014 / SQL Azure)?
Specifically, I want to be able to convert a time into UTC before inserting it into a table. Right now I am doing:
INSERT INTO Incident (IncidentDescription, dtOccurred)
VALUES (@IncidentDescription, DATEADD(hour, 8, @dtOccurred))
I don't want to hardcode "8" and I want it to adjust for daylight savings time (DST).
Is there any concise solution that does not require me to define SQL functions or have lookup tables? I also don't want to use the difference between GETDATE() and SYSUTCDATETIME(), because the timezone of my server is not my local timezone.
I have a query which can return multiple rows. I want to select only the nth row. In other db languages, this is achieved using the OFFSET command, but in SQL Server this command does not exist. I saw that you can hack it using ROWCOUNT and an ORDER BY clause, but I don't have anything to order by. Is there some other way to do this? My query is
Code:
SELECT S.SystemName FROM Projects AS P INNER JOIN ProjectSystems AS PS ON PS.ProjectId = P.ProjectId LEFT JOIN Systems AS S ON S.SystemId = PS.SystemID WHERE P.ProjectId = @ProjectID
This will select an example result of:
Code:
System Name
System 1
Test System 2
Test 123
How can I do something like (psuedo code)
Select Row @row From query
What is the fastest way to select a value offset by n rows from the start row? I used to use a cursor with FETCH ABSOLUTE in Sybase SQLAnywhere, but this is incredibly slow in SQL Server. Here's the current function I'm using:
FUNCTION dbo.TradingDaysBack ( @ItemID int, @FromDate smalldatetime, @DaysBack int )
RETURNS smalldatetime
AS
BEGIN
declare @BackDay int
declare @OADay int
set @OADay = dbo.GetOADate(@FromDate)
declare curDaysBack cursor scroll for
select OADate
fromData_Daily
whereItemID = @ItemID and OADate <= @OADay
order byOADate desc
open curDaysBack
fetch absolute @DaysBack
from curDaysBack
into@BackDay
close curDaysBack
deallocate curDaysBack
if @BackDay is null
begin
set @BackDay= ( select Min(OADate) from Data_Daily where ItemID = @ItemID and OADate <= @OADay )
end
RETURN convert(smalldatetime, @BackDay)
END
The idea is to get the date n rows of data back from the starting date (i.e. 30 trading days back from 12/1/2003). Any ideas?
Please, please, please, please just add limit/offset to the language.
7 years of working around this unnecessary limitation are enough. And that's just me personally, i'm sure there are those out there with a longer history and are more irritated. I know this isn't the first post on this here, nor will it be the last. I think that should be taken as a hint, and something should be done about this. Something like this:
SELECT column FROM table LIMIT 10 OFFSET 10
See, it wasn't that hard =) Now there are no ugly contortions to go through just to paginate the data. And don't recommend I return all 20k rows and do it on the client side, m'kay, or write ridiculous sql. It seems that for all the hard work that went into making those other features work that this could have been done. Don't lie, you know it could have! If there was a voting feature for suggested improvments, or a bug tracker + watch list that functioned the same, it would help. Others do that, as well.
//Another Annoyed DeveloperAnd seeing as there aren't any feature/suggestion channels that work (see:connect), I guess this forum is the best we can do. Sorry.
I have a query in MySQL which needs to be translated to an equivalent in MSSQL Server. The query uses a LIMIT clause with an offset.
select * from test LIMIT 10,5
(meaning from the 10th row containing 5 rows in the result set)
How can we port the same logic to SQL Server ? I know TOP can retrieve top n rows but how can we specify the offset ?
Any help is appreciated !
Hi,
I need the equivalent for below mysql query :
select *from test limit 5, 20
I know that the below is possible in SQL Server :
select top 20 * from test where column1 not in (select top 5 column1 from test).
But my problem is I want a generic solution wherein I will not be aware of the columns available within the table.
Please advice,
- Mira
Hi,
Sorry I have to post this here, but its sort of related to MS SQL anyway
I'm running a PHP system with MS Access. I need to order results in pages. (For those who are familiar with MySqL and Postgresql - I need the equivalent to LIMIT/OFFSET in MS Access).
I know I can "SELECT TOP 50" in my sql - so that solves the number of results per page.
But what about page 2,3,4..etc.... how do I select results from an offset?
There doesnt seem to be a solution for it
Thanks!
I have a store procedure who receive an xml file.
In this store procedure I try to put all record in temporary table for some treatement ..
But I have a problem with one field ...
When I execute the code below I receive this error message :
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
The problem comes from the field DeleteDate from the second row of the xml file
how I can get the correct conversion for this field ?
declare @tbl table(actorPersonId int, personType int, actorType int, person int
, firstName varchar(100), surname varchar(100), phone varchar(100), email varchar(100)
, publication int, logUsr varchar(20), active bit, delDate datetime )
[Code] .....
We recently translated the backend db from Access(97) to SQL Server.We are still using Access frontends. I have an update query in theAccess front end that uses a lookup table to populate fields. Thecommon fields between the table and the lookup table are the primarykey (LocID) and date & time fields. The query is:UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],tblPT.Salinity = [tblPT_Offsets].[Salinity]WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And[EndDate]));This worked fine in Access and seemed to work fine after switching toAccess, but on closer look, there is exactly a 2 day error beingintroduced. A quick search of the newsgroups brings up lots of Accessto SQL date problems, but a 2 day offset seems rather strange? Anyideas??I know the field names Date and Time are inappropriate, but legacyissues are a pain in the butt to resolve!! Could this be a problem?David
View 3 Replies View RelatedI have the follwing function in my SQL data base which is hosted in Azure.
All date and time field in my db are store as UTC and I have a function define as below :
ALTER FUNCTION [dbo].[func_GetCurrentLocalTimeFromUTC]
(
)
RETURNS datetime
AS
BEGIN
DECLARE @OffSet as int = 1
DECLARE @CurrentDate as datetime = getUTCdate()
[Code] ...
Now what I have trying to do is to perform a select statement on a table where I have a dateTime value field and add the proper offset value to the field in order it is display based on where user will run the query. For example if I run this simple query :
SELECT INVENTORYDATE from Inventory
Will return the UTC dateTime value.
How can I use the select statement in order to get the field format with proper offset based on user pc timezone/. Please note that the query will be called from a rrs.
I have following simple code in my stored proc. even I have hard coded OFFSET to non zero, but it always return result from starting point 0. End limit "Fetch Next" is working perfect.Only problem is with start.
SELECT
*
FROM #invoices
ORDER BY #invoices.InvoiceDateTime ASC
OFFSET @StartRow ROWS Fetch NEXT @EndRow ROWS ONLY;
I'm getting a file that essentially has a parameter array: a specific like would have three known values, and then x number of groups of four parts;
The question is how to handle logical groups of parameters; i though maybe by using a modulous on their itemnumber ...
So I can easily use DelimitedSplit8K to definitively find the first thee values, but how do I dynamically get X number of groups of four; eventually i need the first three fields combined with each quadgroup.
So a rough example of the desired output is this:
this:
'03,0000001,USD,010,81257946,,,015,121809761,,'is split into rows with the first three columns included in each row:
030000001USD01081257946NULLNULL
030000001USD015121809761NULLNULL
Here's a setup i've put together:
IF OBJECT_ID('tempdb.[dbo].[#AccountIdentifier]') IS NOT NULL
DROP TABLE [dbo].[#AccountIdentifier]
GO
CREATE TABLE [dbo].[#AccountIdentifier] (
[AccountIdentifierID] INT IDENTITY(1,1) NOT NULL,
[DateReceived] DATETIME NULL DEFAULT getdate(),
[RecordCode] VARCHAR(3) NULL,
[Code] ....
I'm sure I am not undestanding some basic concept here but the following formula always produces an invalid token error at the '-' sign. In this example, I'm trying to subtract out a specific month from the total (this is a simplified example, my actual formula needs to compute a % change over time using lag...)
This produces the invalid token error (it always errors at the '-' in the equation)
with member [Measures].[MyCalcMeasure] as [Measures].[MyBaseMeasure]-([Date Submitted].[Date Submitted YQMD].[month].&[2008]&[1],[Measures].[MyBaseMeasure])
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
But this works
with member [Measures].[MyCalcMeasure] as [Measures].[MyBaseMeasure]
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
As does this
with member [Measures].[MyCalcMeasure] as ([Date Submitted].[Date Submitted YQMD].[month].&[2008]&[1],[Measures].[MyBaseMeasure])
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
What am I missing?
Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks!
Kamen
Hi,
I'm new to these forums (and to SQL Server), so please be gentle with me.
I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.
I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.
I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)
I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.
Many thanks
I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key:
HKLMSoftwareMicrosoftMSSQLServerSetup
It seems that a SQL2005 installation does not have the same default
directory.
Is there a way to query this from the server itself so I don't have to read the registry?
What is the query to get 2nd last record from a table on a specific note_id key?
This is the data I have:
Note_id ======From Date===== =======To Date======
892481====2010-07-27 00:00:00===2010-07-27 00:00:00.000
892481====2010-07-27 00:00:00===2010-07-27 23:36:30.063
892481====2010-11-01 00:00:00===2010-11-02 00:15:23.027
892481====2011-03-08 00:00:00===2011-03-08 13:18:37.873 <====== This is the result i want
892481====2011-04-21 00:00:00===2011-04-21 07:57:20.567
I'll be working on a web project that utilizes sql server 2005. Our web host will be providing the server however I'll need a version of Sql Server 2005 management studio to work with it.
I believe the studio software costs around $50 however the express edition software is free.
So my question is, is the express edition software compatible with the full version of sql server 2005 or only sql server 2005 express edition?
Thanks.
-Goalie35
How is it possible to tell which version of ADO is installed on an XP workstation?Thanks
View 1 Replies View RelatedIs there a way to query a SQL server such that it will return thecurrent counter value of a field that increments automatically (anidentity field)? Using a MAX query might not necessarily give you thecorrect information because it is possible that the high value recordswere deleted. I checked the system tables and none of them appear tohave what I need. I suppose it would be possible to maintain a tablethat lists all the incrementing columns, but ideally there would be away already built into SQL Server.Thanks.
View 3 Replies View RelatedHi People
We want one trigger which captures the data as mentioned below.
Table Name
Date and Time
User
type
Mode of modification
TABLE_ABC
03/07/2007 12:00:04
XCSRaoa
Update
Procedure
TABLE_DEF
03/07/2007 12:00:34
XCSRaoa
Insert
Class Integration SSIS Package
TABLE_GHI
03/07/2007 12:01:04
XCSRaoa
Insert
Procedure
TABLE_GHI
03/07/2007 12:01:34
XCSRaoa
Update
XCSRaoa (Manual)
I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.
Could someone help me achieving this?
Regards
Abhi
Hello,
By using DBCC Page I am able to exemine data entered to row and other changes related to the rows. What I want to know is; is it possible to find out (and how) which Login ID commited these change directly from pages or where do I need to look to find it out?
Any guidance is appreciated....
Hi All,I'm having a bit of a problem with a c# program which creates and index file/table. The number of records I need to process is around 3500, the main loop. Each of thoes records I need to look at a 2 text fields and create an entry in an index table for each valid word found in thoes fields. I call a stored proc to save the key word. So this stored proc get called numerous time per record.What seems to be happening is proceese about 400 main rcords the errors out with the following error message: {"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." }Has anyone had a similar problem? if so any suggestions on how to fix, I've been looking at this problem and recoding for the last 4 days! I need some help! Using SQL Server 2000 & .net v1.1Thanks in advance Davej
View 1 Replies View RelatedHi
I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides.
Thanks
Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?
View 8 Replies View RelatedI'm hoping someone can shed some light on a reliable way to get Reporting Services if you don't have an MSDN subscription.
I have registered copies of SQL Server 2000 Developer and SQL Server 2000 Standard that I bought late last year.
I've had a backorder with MS Parts since January to get updated CDs with Reporting Services on them. Each month, the back order is pushed up another month. The disks are now due sometime in late May. I called the fulfillment center and the operator end didn't seem too enthusiastic about it coming in stock anytime soon.
All 3 versions (Developer, Standard, and Enterprise) are on a long back-order.
I thought I could get by temporarily by using the 90-day trial copy of Reporting Services, but I'm now concerned it might run out before I get my own non-trial version.
Isn't there a way for legit users to get a hold of this product? I'd be happy to download it, but it's not available unless I want to pay add'l money for an MSDN subscription.
_ANY_ suggestions or help appreciated.
Thanks.
dotnetFool
I have quite a few DTS's to work through while analysing current SQL databases for a client. Problem is I can't seem to find a way in SQL (Enterprise Manager) to print the contents of a DTS package. This is quite frustrating in that I have to go an "Design" the package, then click on the properties for each step and copy and paste the contents into Notepad/Wordpad. Surely Microsoft have something to ease the pain?
If anyone can help me on this one, I would be extremely grateful.