Datetime Conversion Problem
Mar 7, 2008
I have a client that records qa data in minute intervals. They like to look at the data in various ways (e.g. 15 minute intervals, x minutes after a shift starts, etc.)
The code below groups data into different time periods. For example, data collected in one minute intervals can be converted into 15 minute intervals. The intervals start from a specified start time. For example, a shift may start a 08:00 but they want to look at 15 minute intervals starting at 08:20 after everybody has settled in.
Unfortunately, it only works some of the time; different intervals work better than others. Five minutes looks good but 15 and 25 minute intervals mess up when there are missing datetime datapoints (i.e. when a group ends at a missing datetime data point it puts in a wrong value).
In sample source data below, 10:50:00 and 10:51:00 are missing. In the result table 10:58:00 should be 10:50:00 (even tho it is missing from the source). BTW, the data values are correct regarding start, end, min, and max values; only datetime is incorrect. The problem is probably coming from the CASE statements in the sample below. Can anybody help?
Sample Source Data:
2007-07-28 10:45:00.000 433.00 433.00 433.00 433.00
2007-07-28 10:46:00.000 433.10 433.10 433.10 433.10
2007-07-28 10:47:00.000 433.10 433.10 433.00 433.10
2007-07-28 10:48:00.000 433.10 433.20 433.10 433.10
2007-07-28 10:49:00.000 433.00 433.00 433.00 433.00
2007-07-28 10:52:00.000 433.20 433.20 433.00 433.00
2007-07-28 10:53:00.000 433.00 433.10 433.00 433.10
2007-07-28 10:54:00.000 433.00 433.20 433.00 433.20
2007-07-28 10:55:00.000 433.20 433.40 433.20 433.40
2007-07-28 10:56:00.000 433.30 433.40 433.30 433.30
2007-07-28 10:57:00.000 433.40 433.40 433.30 433.30
2007-07-28 10:58:00.000 433.20 433.40 433.20 433.30
2007-07-28 10:59:00.000 433.40 433.50 433.40 433.50
2007-07-28 11:00:00.000 433.40 433.50 433.40 433.40
2007-07-28 11:02:00.000 433.50 433.50 433.20 433.40
2007-07-28 11:03:00.000 433.20 433.30 433.10 433.10
2007-07-28 11:04:00.000 433.20 433.30 433.10 433.30
2007-07-28 11:05:00.000 433.30 433.30 433.10 433.10
Sample Result Data:
2007-07-28 10:05:00.000 432.80 432.80 432.10 432.20
2007-07-28 10:20:00.000 432.10 433.50 432.00 433.20
2007-07-28 10:35:00.000 433.20 433.20 432.90 433.00
2007-07-28 10:58:00.000 433.00 433.20 432.90 433.00
2007-07-28 11:05:00.000 433.20 433.50 433.00 433.10
Sample Code:
USE tempdb;
IF OBJECT_ID('#Data') IS NOT NULL
DROP TABLE #Data;
IF OBJECT_ID('#Grouped') IS NOT NULL DROP TABLE #Grouped;
DECLARE @dtStart DATETIME, @dtEnd DATETIME, @interval SMALLINT, @offset SMALLINT;
SET @dtStart = '2007-07-28T08:20:00';
SET @dtEnd = '2007-07-29T15:30:00';
SET @interval = 15;
SELECT @offset = DATEPART(MI, MIN('2007-07-28T08:20:00')) % @interval
FROM [MyDbName].[MySchemaName].[MySrcTableName]
Select [DateTime], [StartVal], [MaxVal], [MinVal], [EndVal], CEILING(DATEDIFF(Minute,@dtStart,[DateTime])/@interval) Frame
Into #Data
From
[MyDbName].[MySchemaName].[MySrcTableName]
WHERE
(convert(varchar, [DateTime],126) BETWEEN '2007-07-28' AND '2005-07-29') AND (convert(varchar, [DateTime],114) BETWEEN '08:20:00' AND '15:30:00') Select
Frame,
MAX(t1.[DateTime]) [Max_DateTime],
MIN(t1.[DateTime]) [Min_DateTime],
MAX(t1.[MaxVal]) AS [MaxVal],
MIN(t1.[MinVal]) AS [MinVal]
Into #Grouped
From
#Data t1
Group By
Frame
Select
(CASE WHEN (DATEPART(MI, Str.[DateTime]) % (@interval + @offset)) = 0
THEN Str.[DateTime]
WHEN
(convert(varchar, Str.[DateTime],114) BETWEEN '15:01:00' AND '15:30:00')
THEN DATEADD(MI, (DATEPART(MI, @dtEnd) - DATEPART(MI, Str.[DateTime])), Str.[DateTime])
ELSE DATEADD(MI, @offset + (@interval - (DATEPART(MI, Str.[DateTime]) % @interval)) - @interval, Str.[DateTime])
END) AS [DateTime],
[StartVal], [MaxVal], [MinVal], [EndVal] Into
[MyDbName].[MySchemaName].[MyDestTableName]
From
( Select
G.[Frame],
G.[Max_DateTime] as [DateTime],
D.[StartVal], G.[MaxVal], G.[MinVal] From
#Data D
Join #Grouped G On D.[DateTime] = G.[Min_DateTime] And D.Frame = G.Frame
) as Str Join
( Select
G.[Frame],
G.[Max_DateTime] as [DateTime],
D.[EndVal]
From
#Data D
Join #Grouped G On D.[DateTime] = G.[Max_DateTime] And D.Frame = G.Frame
) as Erd On Str.Frame = Str.Frame And Str.[DateTime] = Erd.[DateTime]
View 7 Replies
ADVERTISEMENT
Mar 17, 2008
Hello boyz and girlz,
Little question:
I want to write the current date and time into a database with following code:
Dim time As DateTime
time = DateTime.Now
connection.Open()
cmd.CommandText = "INSERT INTO tblOpmerkingen(Time )values('" + time + "')"
cmd.Connection = connection
But: My "time" is DD/MM/YYYY HH/mm/SS
and in my database time = MM/DD/YYYY HH/mm/SS
can somebody help me?
thanx
View 6 Replies
View Related
Jun 16, 2005
Hi,I tried to convert sql datetime to string (hh:mm:ss), or filetime, but i wasn't successful. Will somebody help me with my problem? I don't know how I can solve my problem really.Thank's
View 6 Replies
View Related
Feb 28, 2005
Hello, everyone:
How to convert '173515' to be datetime like "5:33:00 PM". Thanks.
ZYT
View 1 Replies
View Related
May 19, 2008
I have in text field (nvarchar) following date dd-mm-yyyy
and i would like to convert it to smalldatetime field
in format yyyy-mm-dd.
Is there any explicit way to do it?
thank you
View 1 Replies
View Related
Feb 16, 2006
Hi!
I want to get current date and subtract 14 days from that date and return result as int in yyyymmdd-format.
How should I do that?
Thanks in advance, Makkaramestari
View 2 Replies
View Related
Feb 6, 2007
hi, i need to convert datetime as dd/mm/yy hh:mi:ss:mmmAM format,so i used this:
select convert(varchar(20),getdate(),131)
19/01/1428 2:20:22:
i need 06/02/2007 2:20:22pm how to get please tel me
View 6 Replies
View Related
Sep 11, 2007
hi,
i m facing a problem. I want to convert '%' into datetime format.
can any one provide me solution?
Thanx
View 7 Replies
View Related
Jan 17, 2008
Hi,
I am getting the following error when
executing the ExecuteInsert in the code below..:
Conversion failed when converting
datetime from character string.
private bool
ExecuteInsert(String quantity)
{[snip]
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 =
(TextBox)FormView1.FindControl("TextBox1");
Label 1 = (Label)FormView1.FindControl("Label3");
Label 2 = (Label)FormView1.FindControl("Label13");
command.CommandText = "INSERT INTO Transactions (etc,Date,etc)
VALUES (etc,@date,@etc)";
command.Parameters.AddWithValue([snip]);
command.Parameters.AddWithValue([snip]); command.Parameters.AddWithValue("@date",
DateTime.Now.ToString());
command.Parameters.AddWithValue([snip]);
command.Parameters.AddWithValue([snip]);
command.ExecuteNonQuery();
con.Close();
command.Dispose();
return true; } protected
void Button2_Click(object sender, EventArgs e)
{ TextBox TextBox1 =
FormView1.FindControl("TextBox1") as TextBox;
bool retVal = ExecuteUpdate(Int32.Parse(TextBox1.Text));
if (retVal)
Response.Redirect("~/URL/EXTENSION.aspx");
Insert(); } private
void Insert() {
TextBox TextBox1 = FormView1.FindControl("TextBox1") as
TextBox;
ExecuteInsert(TextBox1.Text); }} Thanks if someone can help!Jon
View 2 Replies
View Related
Jan 15, 2006
I am placing DateTime into SQL using an ASP.NET form. The date should be formatted dd/mm/yyyy hh/mm/ss.
I am getting the error below. Is there any way to convert the format of the DateTime function from the ASP.NET end?
Thanks
mes
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"
View 1 Replies
View Related
Aug 8, 2000
Hello, I would appreciate any suggestions
I've got a datetime field that I'd like to store as just the date without the time component, but still to keep it defined as a datetime field. I ran this update statement but this conversion isn't working. Conversion to char gives me what I want but I need to keep the field as a date datatype if possible.
Thanks :)
update
<table>
set
<column>=convert(datetime,convert(char(10),hire_date,101)) )
View 3 Replies
View Related
Aug 22, 2000
The transaction_date is datetime and date1 is nvarchar.
When I run the script:
Insert into payment(transaction_date)
Select convert (datetime, date1) from dep01
I get the following message:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Thanks in advance, Mike
View 1 Replies
View Related
Jul 19, 2004
I have a DTS-package running which imports data from a .csv file to a sql2000 database.
In the file there are some datefields in dd/mm/yyyy format and i want to keep it that way. But after the import the dateformat is yyyy/mm/dd.
Does anybody know how i can prevent this from happening?
Thanks in advance
View 1 Replies
View Related
Aug 3, 2007
I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
Thanks in advance,
sajmera
View 3 Replies
View Related
May 30, 2008
Hi,
Ive imported a data set into SQL. Now on redefining a text field to datetime, most dates that are filled come through okay.
The issue is that there are some empty fields which I'd like for it to stay empty after conversion. Now what happens is that the empty field becomes '01/01/1900' - which is throwing off our queries as we need it to be compared to other date fields.
Is there a way to keep it empty even after the datatype is changed to datetime?
Thanks a bunch!
View 5 Replies
View Related
Feb 6, 2007
Hi all,
I need to convert a char (a) to datetime in the following query:
select *
from table1
where convert (a, datetime) > '01/31/2007'
this query does not work :(
View 1 Replies
View Related
Jul 20, 2006
Hi
Can we convert DateTime to String. If so how?
Thanks in advance.
Mahathi.
View 1 Replies
View Related
Nov 23, 2006
i have another problem.and it's now on converting a char(4) to datetimehere is the situationJ_TIM < F_TIMJ_TIM is datetime while F_TIM is char of 4exampleJ_TIM = 20:30F_TIM = 2030how can i convert F_TIM to datetime so that i can compare them.???thanks
View 3 Replies
View Related
May 21, 2008
Hi everyone,
I have a problem with a datetime field.
In the database is stored as datetime (121 format) ex. 2008-05-20 17:30:00.000
I have a simple select query (no datetime conversions used) and the result I get from the report looks like 5/20/2008 5:30 PM.
I dont want this format so i use this custom dd:MM:yyyy hh:mms
After that i get the dates like this 5/20/2008 5:30 instead of 17:30.
Any suggestions appreciated...
View 1 Replies
View Related
May 21, 2007
I have a column of DT_DBTIME data type in my Flat File source. This column is mapped to DateTime column in the SQL server destination table. A problem is that time values '12:06:39' and '13:37:45' are converted to the dates '12/30/1899 12:06:39 PM' and '12/30/1899 1:37:45 PM' correspondingly.
When I try the similar conversion from the inside Server Management Studio :
SELECT CAST('12:06:39' as DateTime), CAST('13:37:45' as DateTime)
I get the following results:
'1900-01-01 12:06:39.000' and '1900-01-01 13:37:45.000'
As you can see, the date portion is different and I expect that January 1st, 1900 is correct for this conversion. So, my questions are:
1. Why I have "previous" day in the Integration Services transformation?
2. How to fix this problem?
Thank you,
Vlad
View 3 Replies
View Related
May 1, 2007
If I have a variables that are declared as
@Start_Year int
@Start_Month int
How do I convert those variables to datetime format of
MM/1/YYYY
View 1 Replies
View Related
Jan 28, 2008
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
View 5 Replies
View Related
May 25, 2007
I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates.
Below is the code that I am using:
<asp:SqlDataSource
id="SqlDataSource1"
runat="server"
connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>"
selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq"
insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@RepName, @BranchName, @Date, @DueDate, @ByEmail, @ByFax, @Fax, @ProjExp, @PrintQty, @Disc, @Email, @RepEmail)">
<InsertParameters>
<asp:FormParameter Name="RepName" FormField="LoanRepNameBox"/>
<asp:FormParameter Name="BranchName" FormField="BranchList"/>
<asp:FormParameter Name="Date" FormField="CurrentDateBox" Type="DateTime"/>
<asp:FormParameter Name="DueDate" FormField="ReqDueDateBox" Type="DateTime"/>
<asp:FormParameter Name="ByEmail" FormField="ProofByEmailCheckbox" Type="boolean"/>
<asp:FormParameter Name="ByFax" FormField="ProofByFaxCheckbox" Type="boolean"/>
<asp:FormParameter Name="Fax" FormField="FaxNumberBox"/>
<asp:FormParameter Name="ProjExp" FormField="ProjectExplanationBox"/>
<asp:FormParameter Name="PrintQty" FormField="PrintQuantityBox"/>
<asp:FormParameter Name="Disc" FormField="PDFByDiscCheckbox" Type="boolean"/><asp:FormParameter Name="Email" FormField="PDFByFaxCheckbox" Type="boolean"/>
<asp:FormParameter Name="RepEmail" FormField="LoanRepEmailBox"/>
</InsertParameters>
</asp:SqlDataSource>protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance.
Thank you,
Paul
View 2 Replies
View Related
May 12, 2005
i want to insert a datetime type into SQL server from the 'calendar1.SelectedDate'
but when i insert it through SQL command, it display an error like
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated."
so i make some difference in the code like :
dim datePick as DateTime = CDate(clndrEvent.SelectedDate)
then i insert the datePick through SQL Command
at first it turn out just fine, the date succesfully inserted to the database
but when i reOpen the page (restart) to insert another date, it display the same error
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated."
so, if anyone knows whats wrong, i just want to insert date from calendar1.SelectedDate to a datetime type database field
View 4 Replies
View Related
Feb 14, 2006
on a column DateNew = DateTimei am trying :INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')and i get an error :conversion of char data type to datetime data type resulted in an out of range datetime valueI had never this error before , do you know why ?i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langagesthank you for helping
View 14 Replies
View Related
Jul 10, 2006
Hi,
I'm trying to persist a field of type java.sql.Timestamp to a column of type datetime in sql server 2005. I get an exception :
java.lang.UnsupportedOperationException: cannot perform lookups on timestamps. The field i'm trying to persist is a part of a composite primary key. The detailed exception is as follows:
12:13:55,110 WARN [RequestProcessor] Unhandled Exception thrown: class java.lang.UnsupportedOperationException
12:13:55,110 WARN [RequestProcessor] Unhandled Exception thrown: class java.lang.UnsupportedOperationException
12:13:55,120 ERROR [[action]] Servlet.service() for servlet action threw exception
java.lang.UnsupportedOperationException: cannot perform lookups on timestamps
at org.hibernate.type.TimestampType.getHashCode(TimestampType.java:98)
at org.hibernate.type.AbstractType.getHashCode(AbstractType.java:113)
at org.hibernate.type.ComponentType.getHashCode(ComponentType.java:199)
at org.hibernate.engine.EntityKey.getHashCode(EntityKey.java:65)
at org.hibernate.engine.EntityKey.(EntityKey.java:38)
at org.hibernate.event.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:135)
at org.hibernate.event.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:106)
at org.hibernate.event.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:180)
at org.hibernate.event.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:31)
at org.hibernate.event.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:169)
at org.hibernate.event.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:25)
at org.hibernate.event.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:65)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:479)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:474)
I have tried these with my other tables and never got an exception. but this is the first time i'm trying timestamp to datetime conversion with a primary key.
Can anyone suggest what could be the reason?
Thanks, Nithya.
View 2 Replies
View Related
Aug 24, 2007
Conversion failed when converting datetime from character string:How do I trace this problem?
Funnyfrog
View 19 Replies
View Related
Jul 23, 2005
Hi Group,I am new with SQL Server..I am working with SQL Server 2000.I am storing the date in a nvarchar column of atable.... Now I want toshow the data of Weekends..Everything is OK...But the problem isarising with Conversion of nvarchar to date...to identify theweekends...Like..Here DATEVALUE is a nvarchar column...But getting theerror..Value of DATEVALUE like dd-mm-yyyy...04-08-2004-----------------------------------------------------------Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime.---------------------------------------------------------------------------Actual Query-------------------------------Select DATEVALUE,<Other Column Names> from Result whereDatepart(dw,convert(Datetime,DATEVALUE))<>1 andDatepart(dw,convert(Datetime,DATEVALUE))<>7-----------------------------------------------------------Thanks in advance..RegardsArijit Chatterjee
View 3 Replies
View Related
Jul 20, 2005
I am writing a datetime field value to MS SQL Server 7 in the following mannervia a stored procedure:// item to be written is originally a java.util.Date objectjava.util.Date fromDate;// I'm inserting it here into the databasecstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));// the record in the database appears as follows - as I wanted it to..12/23/2004 4:30:43 AMThe problem is reading the date FROM the database back into ANY type of JavaDate-related object. No matter what I try, the hour/minutes/seconds are notreturned, and I desperately need the hour and minutes. I don't want to storethe hours and minutes in another field - it just causes more complications.Does anyone out there know a way to get the ENTIRE date value out of the database?
View 4 Replies
View Related
Jun 5, 2006
How can convert a datetime 'mm-dd-yyyy hh:mm:ss' to yyyymmddhhmmss?
I am using sql 2000.
Thanks in advance
View 3 Replies
View Related
Jan 15, 2008
So what I'm trying to do is audit changes on a server. I'm creating a DDL trigger as below:
Code Block
CREATE trigger DDL_changeTracking_tr
on Database
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
as
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
BEGIN TRY
BEGIN
declare @login varchar(100)
set @login = eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)')
if (@login <> 'sqladmin' and @login <> 'sqlagentadmin')
BEGIN
insert into DBMonitoring..audit_tbl (databaseId, auditTime, loginName, objectName, objectType, eventType)
select
DB_ID() as databaseId
, getDate() as auditTime
, eventData().value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(100)') + '.' +
eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)') as objectName
, eventData().value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(100)') as objectType
, eventData().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)') as LoginName
, eventData().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)') as eventType
END
END
END TRY
BEGIN CATCH
BEGIN
declare @html varchar(max)
select @html = '<html>' + getDate() + '</br>' + eventData().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
+ '</br>' + ERROR_MESSAGE() + '</html>'
PRINT 'Warning: Unable to submit change to audit'
SELECT ERROR_MESSAGE()
exec util_EmailOut_DatabaseMail_prc @from = '<address>',
@to = '<address>',
@cc = null,
@bcc = null,
@subject = 'Change Tracking Insert Failure',
@body = null,
@HTMLBody = @html,
@importance = 1,
@file = null
END
END CATCH
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER DDL_changeTracking_tr ON DATABASE
It inserts the trigger data into:
Code Block
CREATE TABLE audit_tbl (
databaseId int not null,
--auditTime datetime default getDate() not null,
auditTime datetime not null,
loginName varchar(255) not null,
objectName varchar(255) not null,
objectType varchar(25) not null,
eventType varchar(40) not null
)
go
ALTER TABLE audit_tbl ADD CONSTRAINT PK_audit_tbl_databaseId_auditTime_objectName PRIMARY KEY (databaseId, objectName, auditTime)
CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_loginName ON audit_tbl(auditTime, loginName)
CREATE NONCLUSTERED INDEX IX_audit_tbl_auditTime_objectType ON audit_tbl(auditTime, objectType)
In the same database that I've run this one, I'm running this code to test it:
Code Block
create procedure cow_prc
as select 1
go
drop procedure cow_prc
Occassionally when I run this, I get the following error:
Msg 241, Level 16, State 1, Procedure DDL_changeTracking_tr, Line 42
Conversion failed when converting datetime from character string.
I am completely lost on this. I've had 3 fellow DBAs look at it and they're not sure what's going on with it. I've even tried writing the trigger logic as a CTE which using isDate() to make sure that auditTime actually is a date.
Any insight would be greatly appreciated. Thanks in advance.
View 6 Replies
View Related
Mar 11, 2008
I converting DB2 date thru SQL server linked server. The datetime information is select from the DB2 correctly, but when I convert it to a datetime via a SQL function that I wrote, the milliseconds on the converted date are incorrect. The last digiti arbitrarily rounds up/down or does not round at all. I select the dates as a varchar from DB2 and string manipulate the Date field to correctly format the data, perform the convert function and presto the date is incorrect. Here is a code snippet I run on Query analyzer to get the resulse in question.
declare @datestring varchar(50)
declare @date varchar(10)
declare @time varchar(12)
declare @check as varchar(4)
declare @hold as varchar(4)
declare @int as integer
set @datestring = '1995-11-10-09.34.29.465123'
if len(@datestring) > 10
begin
set @datestring = substring(@datestring,1,len(@datestring)-3)
set @date = substring(@datestring,1,10)
set @time = replace(substring(@datestring,12,12),'.',':')
select @datestring, @date, @time,convert(datetime,@date + ' ' + @time)
end
else
select convert(datetime,@datestring)
Resulsts are as follows:
1995-11-10-09.34.29.465 1995-11-10 09:34:29:465 1995-11-10 09:34:29.467
Anyone have any idea what is going happening or any suggestions to correct this?
View 5 Replies
View Related
Jan 30, 2008
Basically, I'm trying to take two dates and find the difference in hours b/t the two, sql server 2000. I've tried change the variables out from datetime to varchars even though it shouldn't matter. When I put the block of code out of the stored procedure and run it in line it works fine, I'M LOST.........
I get the errors everytime it runs into the first block, I get the error again when the block is hit again farther down in the procedure. I get:
Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
ERR dates Aug 4 2005 11:16PM.
Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
ERR dates Aug 3 2005 12:35PM.
Server: Msg 241, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 211
Syntax error converting datetime from character string.
Code:
Declare
,@CodePair varchar(10
,@CodePairTimeDiff dec ,
@tmpTimeVar datetime ,
@tmpTimeCharVar varchar(30)
IF @NextStatusDate IS NOT NULL AND @StatusDate IS NOT NULL
BEGIN
set @tmpTimeCharVar = DATEDIFF(SECOND, @NextSTatusDate, @StatusDate)
set @tmpTimeVar =
(
CASE WHEN @tmpTimeCharVar/3600<10 THEN '0' ELSE '' END
+ RTRIM(DATEDIFF(SECOND,@NextSTatusDate, @StatusDate)/3600)
+ ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) % 60),2)
)
END
IF @@ERROR <>0
PRINT 'ERR dates' + Convert(varchar(20), @NextStatusDate) + '.'
inside update statement
,CodePairTimeDiff = CONVERT(dec, (100 * DATEPART(HOUR, @tmpTimeVar)
+ DATEPART(MINUTE, @tmpTimeVar))) / 60
View 12 Replies
View Related
