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

Conversion Datetime

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

Datetime Conversion

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

Datetime Conversion

Feb 28, 2005

Hello, everyone:

How to convert '173515' to be datetime like "5:33:00 PM". Thanks.

ZYT

View 1 Replies View Related

Datetime Conversion

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

Datetime Conversion

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

Datetime Conversion

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

Conversion From '%' To Datetime

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

Datetime Conversion Error?

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

Datetime And Conversion To Smalldatetime.

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

Datetime Field Conversion

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

Conversion Of Nvarchar To Datetime. Is This Possible??

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

Datetime Conversion From Csv File

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

Float To Datetime Conversion

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

String To Datetime Conversion

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

Conversion Char To Datetime

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

Conversion Datetime To String

Jul 20, 2006

Hi
Can we convert DateTime to String. If so how?
Thanks in advance.

Mahathi.

View 1 Replies View Related

CONVERSION FROM CHAR(4) TO DATETIME

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

Error In Datetime Conversion

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

DT_DBTIME To DateTime Conversion

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

Datetime Conversion Question

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

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

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

Data Conversion (String To DateTime)

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

Urgent HelP ! About The DateTime Type Conversion

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

Conversion From CHAR To DATETIME Error

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

Timestamp To Datetime Conversion In Primary Key

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

Conversion Failed When Converting Datetime From Ch

Aug 24, 2007

Conversion failed when converting datetime from character string:How do I trace this problem?

Funnyfrog

View 19 Replies View Related

Conversion Error...nvarchar To Datetime

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

Datetime JDBC Date Conversion

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

TSQL Question - Datetime Conversion

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

Errors With DateTime Conversion -- This One's A Weird One.

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

DB2 To SQL Datetime Conversion (Rounding) Problems

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

Datetime And Varchar Conversion Issues

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







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