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