Datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Datetime

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2006-07-20 : 13:06:29

(PDH-CSV 4.0) (GMT Daylight Time)(-60)
07/20/2006 17:23:12.562
07/20/2006 17:24:12.562
07/20/2006 17:25:12.562
07/20/2006 17:26:12.562

The above is the sample date format i got from the Sql server performance monitor and i am trying to insert these rows into the Sql server table with the column datetime data type

it throws the following error

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

How do i proceed???

thanks
Vic

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 13:24:12

I had no problem inserting those values in a datetime column.

create table #tbl
( dts datetime)

insert into #tbl values('07/20/2006 17:23:12.562')
insert into #tbl values('07/20/2006 17:23:12.562')
insert into #tbl values('07/20/2006 17:24:12.562')
insert into #tbl values('07/20/2006 17:25:12.562')
insert into #tbl values('07/20/2006 17:26:12.562')

drop table #tbl

What exactly are you trying to do?

svicky9
Posting Yak Master

232 Posts

Posted - 2006-07-20 : 13:30:00

i am trying to import them from the CSV files to the Sql server table

Vic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-20 : 13:43:28

Before you try to convert them, set your dateformat to mdy.

set dateformat mdy

CODO ERGO SUM

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 14:33:23

What are you using to do the import? I'm thinking that header may be causing part of the problem.

svicky9
Posting Yak Master

232 Posts

Posted - 2006-07-21 : 09:24:51

Hi

I am doing the performance monitoring on the Sql Server and i created the following table so that i can import the CSV output from the performance monitor to the Sql server using opensource

This is the Table
CREATE TABLE [PerfmonDataCustomer01] (
[CounterDateTime] [datetime] NOT NULL,
[Page Reads/sec] [numeric](18, 2) NULL,
[Pages/sec] [numeric](18, 2) NULL
)
GO
ALTER TABLE [dbo].[PerfmonDataCustomer01] WITH NOCHECK ADD
CONSTRAINT [PK_PerfmonDataCustomer01] PRIMARY KEY CLUSTERED
(
[CounterDateTime]
)
GO

The sample CSV file is

(PDH-CSV 4.0) (GMT Daylight Time)(-60) \Memory\Page Reads/sec \Memory\Pages/sec
07/20/2006 17:14:12.562
07/20/2006 17:15:12.562 67.9940618 294.4464594
07/20/2006 17:16:12.562 41.90319097 155.2284875
07/20/2006 17:17:12.562 58.95432118 316.7232149
07/20/2006 17:18:12.562 47.98687613 215.065878
07/20/2006 17:19:12.562 42.91984189 193.0309472
07/20/2006 17:20:12.562 74.03881312 477.1519818
07/20/2006 17:21:12.562 37.86945505 255.0187774
07/20/2006 17:22:12.562 27.65205053 114.5251592
07/20/2006 17:23:12.562 1.066745624 6.517149049
07/20/2006 17:24:12.562 0 0
07/20/2006 17:25:12.562 0 0
07/20/2006 17:26:12.562 0 0
07/20/2006 17:27:12.562 1.900146844 6.15047531
07/20/2006 17:28:12.562 0.300022328 1.416772107
07/20/2006 17:29:12.562 0 0
07/20/2006 17:30:12.562 0.450033316 0.350025913
07/20/2006 17:31:12.562 0 0
07/20/2006 17:32:12.562 0 0
07/20/2006 17:33:12.562 0 0
07/20/2006 17:34:12.562 0 0
07/20/2006 17:35:12.562 0 0
07/20/2006 17:36:12.562 0 0

The query i execute for importing is as follows

INSERT INTO [PerfmonDataCustomer01] (
[CounterDateTime]
,[Page Reads/sec]
,[Pages/sec]
)
SELECT
[(PDH-CSV 4.0) (GMT Daylight Time)(-60)]
,cast([\Memory\Page Reads/sec] as float)
,cast([\Memory\Pages/sec] as float)
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
-- csv file on a local drive
'Data Source=C:\PerfLogs;Extended properties=Text')...SQLperfmon_07201714#csv

It gives me the following error

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

I checked the Default language of the user as well as the server and also changed the format...Is there anything else i need to do

thanks
Vic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources