Inserting data in a table from another table

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

General SQL Server Forums

New to SQL Server Programming

Inserting data in a table from another table

Author

Topic

mathumitha
Starting Member

1 Post

Posted - 2006-12-07 : 06:01:10

I have a pb when i transfer data from a table named INCIDENT to a
table GI_INCIDENTS....
In the table INCIDENT i have startdate,enddate,starttime,endtime
And in the table GI_INCIDENTS i have startdate and enddate -->format
yyyy/MM/dd hh:mm:ss.ttt..
INCIDENT is a migrated table from access...Then with a query i
transfered datas to GI_INCIDENTS...
The pb is in INCIDENT Table, date of beginning incident is
(2003/06/18 ) but when i execute my insert query,in the table
GI_INCIDENTS, date of beginning incident is (2003/06/06)...
So i have 2 days delay in the all colums...
INCIDENT-->enddate (2006/11/30) GI_INCIDENT-->enddate(2006/11/28)
I don't understand the fact...

The query:

Insert into
GI_INCIDENTS(GIIN_ID,GIIN_STA_INCIDENT,STARTDATE,ENDDATE,GIIN_TYPE,GIIN_RESUME,GIIN_DESCRIPTION,GIIN_IMPACT_ANTENNE,GIIN_INITIATEUR)
select NUMINCIDENT,CODETAT,STARTDATE+ ' ' + STARTTIME, ENDDATE + ' '
+ ENDTIME,CODETYPE,NATURE,DESINCIDENT,CODBLOQ,NUMEXPL From INCIDENT
set IDENTITY_INSERT GI_INCIDENTS off

Thanks a lot for your help..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 06:24:25

That is beacuse date ZERO for ACCESS is December 30, 1899 and SQL Server has a date ZERO or January 1, 1900.
Which incidentally is exactly two days.

INSERT INTO	GI_INCIDENTS
		(
			GIIN_ID,
			GIIN_STA_INCIDENT,
			STARTDATE,
			ENDDATE,
			GIIN_TYPE,
			GIIN_RESUME,
			GIIN_DESCRIPTION,
			GIIN_IMPACT_ANTENNE,
			GIIN_INITIATEUR
		)
SELECT		NUMINCIDENT,
		CODETAT,
		DATEADD(day, DATEDIFF(day, 0, STARTDATE), 0) + STARTTIME - DATEDIFF(day, 0, STARTTIME),
		DATEADD(day, DATEDIFF(day, 0, ENDDATE), 0) + ENDTIME - DATEDIFF(day, 0, ENDTIME),
		CODETYPE,
		NATURE,
		DESINCIDENT,
		CODBLOQ,
		NUMEXPL
FROM		INCIDENT

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-07 : 08:37:34

<<
That is beacuse date ZERO for ACCESS is December 30, 1899 and SQL Server has a date ZERO or January 1, 1900.
Which incidentally is exactly two days.
>>

Why is the variation?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 08:40:29

I don't know, but it took me some time to figure it out.
First I just wrote +2 when exporting to SQL.

Peter Larsson
Helsingborg, Sweden

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-08 : 16:06:46

quote:
Originally posted by madhivanan
Why is the variation?

Madhivanan

Failing to plan is Planning to fail

Most likely because COM on Windows (ActiveX) uses the 12/30/1899 date, and that's where Access got it. SQL Server isn't automatable like the Office applications are, and therefore didn't need to be COM-compliant.

Ken

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 02:06:33

Thanks Ken

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources