Strange Date/time Anomaly, Or Am I Just Stoopid?

Aug 30, 2005

To set up the problem, paste this into QA:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[WorkOTRate]
GO

CREATE TABLE [dbo].[WorkOTRate] (
[TimeFrom] [smalldatetime] NOT NULL ,
[TimeTo] [smalldatetime] NOT NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.WorkOTRate (TimeFrom, TimeTo, RateMultiplier)
VALUES ('18:00:00', '23:59:59', 1.2)

SELECT TimeFrom, TimeTo, RateMultiplier FROM dbo.WorkOTRate

This gives the following result:

1900-01-01 18:00:001900-01-02 00:00:001.2

So, it's storing the time 23:59:59 as midnight. That's odd.

(NOTE: If you rescript the table using datetime instead of
smalldatetime types, the data are stored correctly.)

It gets worse (or better, if you like perversity).

If I go to Enterprise Manager, right-click on WorkOTRate and select
"Open Table" -> "Return All Rows" I get:

01/01/1900 18:00:0002/01/19001.2

So, I bite the bullet and change the two column types to datetime,
clear out the old data and run the INSERT again. The data looks better
now.

Go back to the view in EM.

If I put the cursor in a new row, and type into the TimeFrom column
18:30:00 and the TimeTo column 19:30:00 and the RateMultiplier column
1.3, and refresh the data by pressing the red shriek !, I get this:

01/01/1900 18:00:0001/01/1900 23:59:591.2
18:30:0019:30:001.3

If I re-run the SELECT from the QA, I get this:

1900-01-01 18:00:00.0001900-01-01 23:59:59.0001.2
1899-12-30 18:30:00.0001899-12-30 19:30:00.0001.3

Is it just me, or does this seem to be remarkably inconsistent?

Edward

View 2 Replies


ADVERTISEMENT

Real Time Anomaly Detection

Jan 20, 2008



Hi,
I'm would like know if the analysis service data mining enables to detect anomalies from "normal" behavior/patterns of data (1), and alert about such anomalies when detected (2). both above sql server relational DB (3).

Thanks,
Eyal

View 5 Replies View Related

Execution Time Anomaly In Stored Procedures In SQL Server 2005

Dec 3, 2006

Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?

View 4 Replies View Related

Dateadd Date Calculation Anomaly

Apr 3, 2008

I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).

--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))


when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.

Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).


Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.

View 8 Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

Jun 30, 2007

This is driving me nuts..



I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.



I'm struggling with this for a quite a while and I'm not able to get it working.



I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000



the space in MM : SS is intentional here, since without that space it appread as smiley



I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data



I'm struck with error for hours now. Any pointers would be helpful.



Thanks

View 3 Replies View Related

Strange Problem With Time Series Data On Time Series Chart

May 31, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?



Hope it is clear for your help.

I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,

View 1 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Very Strange Connection Time Out

May 25, 2004

I am sure I am using the same connection string for all connection.open, and closing all connections as soon as possible… but

When I try to debug my web app with vs, it runs for some time then I get:

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 exception

However, if I just access the application normally form the url (not using vs.net) it runs flawlessly.

Has some one had the same problem? i can't debug help!!

View 3 Replies View Related

Strange Date Format

Jan 31, 2007

Hello,
I need to convert the following date format from this:
Dec 21, 2006
to this:
 12-21-2006
Any help would be greatly appreciated. Thanks.
 

View 3 Replies View Related

Strange Data Type Displayed On Time Series Algorithm Model

Jun 5, 2007

Hi, all,

Again I encountered a very strange problem which displayed the predicted attribute values as percentage format? The data type of the attribute is actually double, why is that?

That's really frustrated.

Thanks a lot in advance for your kind advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 3 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

Strange Out Of Range Date In A Datetime Field

Sep 26, 2006

How is that I have stuff like...

13415-10-14 72:00:56.973

and -21858-02-28 390:54:27.200

in a datetime field when BOL says...

"Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds."

Not only that but I can't find where this field is getting written to in my trace

View 3 Replies View Related

How To Convert A Date (date/time) To A Julian Date

Jun 13, 2002

In SQL Server 2000:

How do I convert a Julian date to a Gregorian date?

How do I convert a Gregorian date to Julian?

Examples please.

Many thanks in advance.

Gary Andrews

View 2 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

Mar 18, 2014

I have the following

Column Name : [Converted Date]
Data Type : varchar(50)

When I try and do month around the [Converted Date] I get the following error message

“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”

My Query is

SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]

View 7 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?

Nov 13, 2007

Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).

I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.

The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?

Please advise. Thanks.

Best.
K7

View 1 Replies View Related

Sharepoint Integration With Erroneous Date Format In Date Time Picker

Sep 5, 2007

Dear Expert!

A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).

Example:
When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007.
When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.

The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.

The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.

The test report has no database connection whatsoever.

When setting the site to US, the timestamp wont appear at all.

The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.

Any advice would be greatly appreciated.

Kind Regards

Johan Rastenberger

View 1 Replies View Related

How To Find Out Date/time When Row Was Updated Last Time

Jan 15, 2002

Hello,
we need to track date/time of last update for each record in a table.

As we understand it, we can't use field type Timestamp as this type does
not use dates/times.

Is there any SQL function available which we can bind to a column or
do we really have to use triggers?

Greetings from Mannheim, Germany
Ricardo

View 2 Replies View Related

How To Find Out Date/time When Row Was Updated Last Time

Jan 15, 2002

Hello,
we need to track date/time of last update for each record in a table.

As we understand it, we can't use field type Timestamp as this type does
not use dates/times.

Is there any SQL function available which we can bind to a column or
do we really have to use triggers?

Greetings from Mannheim, Germany
Ricardo

View 1 Replies View Related

Update Time In Date-time Field?

Nov 11, 2013

I want to update the time in a datetime field with the current time.

Fields current value is:

2013-11-11 00:00:00.000

I want to insert this into another table and when I do I want to grab the current time and update that field.

field name: picked_dt
Table: oeordlin

or is there a way through sql to update the time when the picked_dt is updated?

View 2 Replies View Related

Add Time To Datetime Value And Split Into Date And Time

Jun 12, 2007

Hi



i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).



could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).



Thanks

V.

View 3 Replies View Related

SSRS Report Paramets - Date Type Datetime !Strange Behaviour!

Dec 12, 2007

I have, I think a strange one.

I have a simple query which has a parameter 'between @Start and @Finish'
My user within the SQL management studio is set to British English.
My report language properties are set to English (United Kingdom)

If I set the parameter to data type string and use start and finish of 31/12/2007, 30/12/2008 the report works fine.
If I change the data type of the parameter to Datetime (to use the calander pickers) the date values are no longer valid.

Where else should I be looking for regional settings? Or is there another reason for this?

I'd appreciate any help.

Cheers

View 3 Replies View Related

Display Only The Date Part Of A Date And Time Field?

Mar 16, 2014

I want to display only the date part of a date field which contains both date & time information.

For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.

I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.

View 3 Replies View Related

How To Convert Date-field To Date With Time Zone

Jul 4, 2014

I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.

ALTER PROCEDURE [dbo].[ByDateRange]

@Feed VARCHAR(50),

@CreatedDate DATETIME

select * from Date_table where Create_TM = @CreatedDate

View 1 Replies View Related

Problem With Current Date For Date & Time Field

Dec 29, 2005

I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett

View 7 Replies View Related

Putting Date Or Date && Time In A Column Thats Going To Be Heavily Used?

Oct 4, 2007

Hello,

We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:

AuditID - the audit action (insert, modify - old, modify - new, deleted)
AuditDate - date and time of action
AuditUser - User who did it...

At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.

I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...

Question: Whats better for performance:

1. Separating out the date and time and doing a clusterd index on the date.

2. Keeping date and time in the same column and just use a normal index.

3. Better ideas?

Thanks,
Bradley

View 1 Replies View Related

DB_NAME Anomaly

Dec 7, 2004

On my SP3 db_name(0) and db_name(1) are returning the same thing. Can anybody else try this? Beta of Yukon is doing the same thing...But the following code returns 1: select db_id(db_name(0))

View 3 Replies View Related

Anomaly With NULL

Dec 26, 2014

why these two SQL codes return same result 0?

select CASE WHEN NULL IN ('123') THEN 1 ELSE 0 END AS Test
select CASE WHEN NOT NULL IN ('123') THEN 1 ELSE 0 END AS Test

View 2 Replies View Related

Formatting Date SQL Date To Remove Time

May 2, 2008

Hi,I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:"Select DISTINCT([StartDate]) From [Events]"How can this be done?Thanks,Curt.

View 4 Replies View Related

How To Convert Date String To Date Time

Jan 8, 2013

I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.

select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2

How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.

View 14 Replies View Related

Date Time Format For Date Parameter

Jan 24, 2008



Hello,

I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.

how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000

thank you
Kiran

View 5 Replies View Related

Getting Time From Date/time Column

May 16, 2008

How can I obtain just the time portion from a date/time column?
My data contains "2008-05-19 09:30:00.000"
Actually, all I want/need is the hh:mm part of it.

Thanks,
Walt

View 2 Replies View Related

Interesting Peculiarity (another Anomaly?)

Dec 8, 2004

Run the following: declare @s1 varchar(10), @s2 varchar(10) declare @t table( recordid int identity(1,1)not null,field char(1)not null) insert @t (field) select 'a' union select 'b'select @s1 = '', @s2 = ''select @s1 = @s1 + field +',' from @t order by 1select @s2 = @s2 + field +','from @t order by recordidselect[Weird]=substring(@s1, 1,(datalength(@s1)-1)),[Not]=substring(@s2, 1,(datalength(@s2)-1))

View 2 Replies View Related







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