UTC V.s Local Time

Mar 7, 2007

I am a newbie to SQL server. I have datetime values stored as UTC in a table. What I want is that when I retrieve the records it should display these datetime values in local datetime. IS there a query or procedure someone can help me with?
Also is there some way of inserting the records in local time instead of UTC.

nivea

View 4 Replies


ADVERTISEMENT

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Aug 7, 2007



Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View 5 Replies View Related

Printing Local Time Zone In Time Format

Nov 16, 2007

I am in need of a format string or simple vb code that can add the local time zone to the end of a time field.. Something like:
9:36 AM EST
9:36 AM PST

This timezone will just come from the machine that the reporting server is on. I don't see this listed as a standard format, and I have come up empty so far in my research - anyone got a solution for this?

Thanks a bunch!

View 4 Replies View Related

Convert UTC Time To Local Time

Dec 29, 2005

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

 

Thanks for your help.

 

 

View 13 Replies View Related

Convert UTC To Local Time

Aug 1, 2014

I have a stored procedure, it is accepting a parameter which is in local datetime format. But the front end application is passing UTC date format. How I can convert UTC date time to local date time...

View 2 Replies View Related

GETDATE() And The Local Time Zone

Mar 14, 2007

I have used the GETDATE() function within an expression to create a directory name based on the current date. I am in the Sydney time zone and the new day's folder name doesn't change until after 11 am - so GETDATE() is picking up the date and not adjusting for the time zone. How do I either set the time zone within the package or make the GETDATE() function look at time zone of the system on which it is run?

View 3 Replies View Related

Local Time To UTC Time?

Sep 22, 2004

I have a column in a database that holds a GETDATE() value. I need to convert that to the UTC equivelent. How can i go about doing this? Going forward i am using GETUTCDATE(), but i need to convert some data fields from the date thats there to its UTC equivelent. (SQL 2000)

Thanks!

View 1 Replies View Related

How To Make Aspnet DB Use Local Time Zone?

Mar 12, 2006

I created aspnet DB on SQL Server 2K using aspnet_regsql utility. Everything works fine except the DateTime fields in all tables are using a wrong time zone. How do I set it to use my local time zone?
Any help will be greatly appreciated.

View 1 Replies View Related

Retrieve Local Time From Remote SQL Server Computer

Feb 6, 2004

Hi all

I am about updating some fields with local date and time using blw API
Quote
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Public Function LocalDateTime()

Dim MyTime As SYSTEMTIME

GetLocalTime MyTime

Debug.Print "The Local Date is:" & MyTime.wMonth & "-" & MyTime.wDay & "-" & MyTime.wYear
Debug.Print "The Local Time is:" & MyTime.wHour & ":" & MyTime.wMinute & ":" & MyTime.wSecond

End Function
Unquote
This works fine however due to it's not really easy for an administrator to keep checking every times wheter local date and time are correctly updated for each front end users computer i would that to reconsider my function and making it to read local date and time from remote SQL Server. my concern is to help administrator to save time as he should only need to ensure that Server is running with correct time...

thkx for any advise

View 1 Replies View Related

Converting UTC Datetime Values To Local Time Zones

May 20, 2008



I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:


SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4


-- =============================================

-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO

View 6 Replies View Related

Power Pivot :: How To Convert Date / Time From UTC To Local (CET) Considering DST

Oct 27, 2015

I am importing data from Dynamics CRM online using ODATA in Power Query. All datetimes in the database is stored in UTC and I need to convert these into CET. But it's not as simple as just to add 1 hour to the datetime from the database because due to Daylight. Savings time on half the dates I need to add 2 hours - how can I do this?

View 3 Replies View Related

Transact SQL :: Convert Local Date Time Field To GMT?

Jul 28, 2015

I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]

[URL]

View 5 Replies View Related

Reporting Services :: Display Report Execution Time - Local

Sep 24, 2015

ExecutionTime gives server time, How can I get local time ? I'm planning to create a report part and use it across all the reports to display report execution time.

- Reports are being opened in Citrix
-MS SQL Server 2008 R2

View 3 Replies View Related

The Remote Copy Of Database X Has Not Been Rolled Forward To A Point In Time That Is Encompassed In The Local Copy

May 11, 2006

Hi,

I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:


alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go


The error message is:

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)

1. backup database DBmirrorTest on SQL1

2. backup database log

3. copy db and log backup files to SQL2

4. restore db with norecovery

5. restore log with norecovery

6. create endpoints on both SQL1 and SQL2

CREATE ENDPOINT [Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER)

7. enable mirror on mirror server SQL2

:connect SQL2

alter database DBmirrorTest

Set Partner = N'TCP://SQL1.mycom.com:5022';

go

8. Enable mirror on primary server SQL1

:connect SQL1

alter database DBmirrorTest

Set Partner = N'TCP://SQL2.mycom.com:5022';

go

This is where I got the error.

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy



Thanks for any help,

KT

View 8 Replies View Related

Local SQL Server Instances Not Showing Up On Local Servers Tab Of Management Studio Logon Screen

Oct 2, 2007

We have a 64-bit VM server running SQL Server 2005. The SQL Server on this particular VM server has 6 local instances installed. On the Management Studio logon screen I can type the full name of the local instance and connect to it, however if I press the drop down in the Server name field, choose Browse and select the Local Servers tab there is nothing listed under Database Engines.

Any idea why the 6 local instances don't show up under Database Engines? This is preventing me from installing a vendor application because their installer looks for local SQL Server instances on this server, but if SQL Server won't even show the local instances then the installer doesn't see them either.

Any help is greatly appreciated.

Thanks,
Craig

View 3 Replies View Related

Not Able To Connect To The Local Database With (local) As Server Name

Jun 7, 2006

I am facing a problem in connecting to the local database with server name as (local).

I have installed SQL Server 2005 in my machine. When I try to connect to the SQL server with the server name as SUNILKUMAR I am able to connect but when I try to connect to the same server with the server name as (local) I am not able to connect. SUNILKUMAR is my machine name and SQL server is running locally.

if anyone can help me what is the problem in this case it is highly appriciated.

View 7 Replies View Related

Need Help - Local Synchronization Between SQL Mobile And Local SQL Database

Dec 21, 2005

Hi Everyone

I am at the stage of architecting my solution

My goal is to develop the system on a windows application and pda

There is a central server which will create a publication called inventory

The laptops which host the windows application will be subscribers to the central server using merge replication

The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server

I have been reading for days and I am still unsure whether this is possible to do.

I know Appforge provide a conduit for palm to access synchronization but not local sql databases

I would appreciate your help immensley

View 7 Replies View Related

Moving A SQL Server 2000 Database From A Local Drive To Another Local Drive

Jan 31, 2008

Being a very novice SQL Server administrator, I need to ask the experts a question.

How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?

Thanks,
Rick

View 4 Replies View Related

Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed

Dec 7, 2006

This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee

View 3 Replies View Related

SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?

Apr 25, 2014

Sample Table

USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

It seems to work fine with one million records.

Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.

View 7 Replies View Related

SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?

Jun 11, 2015

Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.

View 5 Replies View Related

Integration Services :: Exclude Time In Date Time Variable In SSIS For Loop?

Oct 22, 2015

I am trying to load previous days data at 3 am via a SSIS job.

The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.

Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000

How can i do this? 

View 2 Replies View Related

Transact SQL :: Update Time Portion Of DateTime Field With Time Parameter

Oct 3, 2015

I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000

ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime

[code]...

View 14 Replies View Related

It Takes A Long Time To Insert The First Record Each Time When The Program Start

Dec 15, 2006

I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

        cn.Open()
        sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
        scb = New SqlCeCommandBuilder(sda) 
        sda.Update(dataset)
        cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

 However, when I give up the dataset and using the following code:

            cn.Open()
            Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

           cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
            cn.Close()
            StartTime = Environment.TickCount

 I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

View 1 Replies View Related

Transact SQL :: Converting 24hrs Time To 12 Hours Time Query Format?

Apr 21, 2015

SELECT 
    CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance

o/p
indate   04/18/2015
time part :17:45:00

I need to convert this 17:45:00 to 12 hours date format...

View 8 Replies View Related

Problem With Getdate() In Transaction Takes The Insert Time Instead Of The Commit Time

Nov 12, 2007

Hi,

We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.

If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.

We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.

We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:


/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()

insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()

update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE


Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).

Regards,

Mark

View 8 Replies View Related

DB Engine :: Time Testing Azure Point In Time Database Restores?

Sep 21, 2015

I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.

View 3 Replies View Related

SQL 2012 :: Converting Time String In Temp Table To Military Time Then Cast As Integer?

Dec 26, 2014

I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:

Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')

how this can be done while my temp table is in session?

View 2 Replies View Related

SQL Server 2008 :: Displaying Transaction Time Punch Data In A Time Card Form?

Oct 7, 2015

I have a table called employee_punch_record that we use to store employee time clock punches.

The columns are:

employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident

Here are some examples of a record:

bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0

We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.

I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?

View 0 Replies View Related

A Time Series Prediction Was Requested With A Start Time Further In The Past Than The Internal Models Of The Mining Model

Feb 19, 2008

Hi all,

I have a very simple time series model which processing works fine without any problem. However when I run the following query

SELECT

[TimeSeries].[PriceChange],

[TimeSeries].[Symbol],

PredictTimeSeries(PriceChange, -3, 2)

From

[TimeSeries]

WHERE

[TimeSeries].[Symbol] = 'x'


I get the following error:

TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.

The following is the excerpt of the minding model script related to the two parameters:


<AlgorithmParameters>

<AlgorithmParameter>

<Name>MISSING_VALUE_SUBSTITUTION</Name>

<Value xsi:type="xsdtring">Previous</Value>

</AlgorithmParameter>

<AlgorithmParameter>

<Name>HISTORIC_MODEL_GAP</Name>

<Value xsi:type="xsd:int">1</Value>

</AlgorithmParameter>

<AlgorithmParameter>

<Name>HISTORIC_MODEL_COUNT</Name>

<Value xsi:type="xsd:int">10</Value>

</AlgorithmParameter>

</AlgorithmParameters>


These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?



View 3 Replies View Related

Reporting Services :: SSRS Loses Ability To Delegate User Credentials To Database Time To Time?

Apr 30, 2015

we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.

View 2 Replies View Related

Help Writing Query (find The Entry With The Closest Time Given A Time)

May 26, 2005

Hi,

I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.

Here's what I have so far:


Code:


select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)



But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

Does anyone know how i could do this? Thanks a lot for any help!

View 2 Replies View Related

Optimization Advice (CPU Time = 15203 Ms, Elapsed Time = 8114 Ms.)!!

Aug 17, 2007

Ok, so I have some horribly convuluted SQL that I would love to optomize. I'm not happy leaving it in it's current state, that's for sure!

I'm currently working on our test bed servers, so obviously my stats are out because of the "crap-ness" (yes, that's the technical term) of the hardware, but still, it should NEVER need to take this long!!

Basically, the issue arises in the nasty join to the career table (one employee can have multiple career lines). Just to make things complicated, employees can have any number of career records on any given date, these can even be input for future career events. The following SQL picks out the latest-current career date for each employee based on the career_date being <= GetDate() and the date of entry for this date being the greatest.

E.g.
career_date | datetime_created
2009-01-01 | 2006-05-05 13:55:21.000
2007-01-01 | 2006-05-05 13:54:18.000
2007-01-01 | 2006-05-05 13:52:55.000

From the above we want to return
2007-01-01 | 2006-05-05 13:54:18.000

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT a.sAMAccountNameAs 'sAMAccountName'
, a.userPrincipalNameAs 'userPrincipalName'
, 'TRUE'As 'Modify'
, RTRIM(e.unique_identifier)As 'employeeID'
, RTRIM(e.employee_number)As 'employeeNumber'
, RTRIM(e.known_as)
+ CASE WHEN RTRIM(e.surname) IS NOT NULL THEN
' ' + RTRIM(e.surname) ELSE NULL ENDAs 'displayName'
, RTRIM(e.known_as)As 'givenName'
, RTRIM(e.surname)As 'sn'
, RTRIM(c.job_title)As 'title'
, RTRIM(c.division)As 'company'
, RTRIM(c.department)As 'department'
, RTRIM(l.description)As 'physicalDeliveryOfficeName'
, RTRIM(REPLACE(am.dn,'\',''))As 'manager'
, t.full_mobile
+ CASE WHEN RTRIM(t.mobile_number) IS NOT NULL THEN
' (DD: ' + RTRIM(t.mobile_number) + ')'ELSE NULL END
As 'mobile'
, t.mobile_numberAs 'otherMobile'
, ad.address_ad_countryAs 'c'
, ad.address_ad_address1
+ CASE WHEN ad.address_ad_address2 IS NOT NULL THEN
', ' + ad.address_ad_address2 ELSE NULL END
+ CASE WHEN ad.address_ad_address3 IS NOT NULL THEN
', ' + ad.address_ad_address3 ELSE NULL END
+ CASE WHEN ad.address_ad_address4 IS NOT NULL THEN
', ' + ad.address_ad_address4 ELSE NULL END
+ CASE WHEN ad.address_ad_address5 IS NOT NULL THEN
', ' + ad.address_ad_address5 ELSE NULL ENDAs 'streetAddress'
, ad.address_ad_poboxAs 'postOfficeBox'
, ad.address_ad_cityAs 'l'
, ad.address_ad_CountyAs 'st'
, ad.address_ad_postcodeAs 'postalCode'
, RTRIM(ad.address_ad_telephone) +
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN
' (Ext: ' + RTRIM(a.othertelephone) + ')'
ELSE
CASE WHEN RTRIM(a.othertelephone) IS NOT NULL
AND RTRIM(ad.address_ad_telephone) IS NULL THEN
'Ext: ' + RTRIM(a.othertelephone)
ELSE NULL
END
ENDAs 'telephoneNumber'
FROM employee e
LEFT
JOIN career c
ON c.parent_identifier = e.unique_identifier
AND c.career_date =(
SELECTmax(c2.career_date)
FROMpwa_master.career c2
WHEREc2.parent_identifier = c.parent_identifier
ANDc2.career_date <= GetDate()
)
AND c.datetime_created =(
SELECT max(c3.datetime_created)
FROMpwa_master.career c3
WHEREc3.parent_identifier = c.parent_identifier
ANDc3.career_date = c.career_date
)
LEFT
OUTER
JOIN AD_Import am
ON am.employeeNumber = c.manager_number
INNER
JOIN AD_Import a
ON a.employeeID = e.unique_identifier
LEFT
JOIN AD_Telephone t
ON t.unique_identifier = e.unique_identifier
LEFT
JOIN AD_Address ad
ON ad.address_pwa_location = e.location
LEFT
JOIN xlocat l
ON l.code = c.location
WHERE (a.employeeNumber IS NOT NULL
OR a.employeeID IS NOT NULL)


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1706 row(s) affected)

Table 'AD_Import'. Scan count 4, logical reads 106, physical reads 0, read-ahead reads 0.
Table 'AD_Address'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'AD_Telephone'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 868, logical reads 956, physical reads 0, read-ahead reads 0.
Table 'xlocat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.
Table 'career'. Scan count 5088, logical reads 2564843, physical reads 0, read-ahead reads 0.
Table 'people'. Scan count 1697, logical reads 5253, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 826, logical reads 914, physical reads 0, read-ahead reads 0.


SQL Server Execution Times:
CPU time = 15203 ms, elapsed time = 8114 ms.

Any advice on what I can do to optomize?

Oh judt to point out that "employee" is a view on the "Table 'people'."
EDIT: I know it's pointing out the obvious, but I'm pulling out the managers "DN" from AD_Import based on the manager_number and employeeNumber matching.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved