October 26, 2009 at 12:16 am
Hi
heres the deal....
I am migrating a large user forum from one host to another and they both "run" sql server 2005. Trouble is they set them up differently so that when I try and import the data from the old host to the new one I get an error. I figured out that it was to do with date format and indeed when I manually vhange the date the import works. The 'old' data has the date format as follows:
'13/01/2008 22:42:20'
but my new server requires the date in this format:-
'2008-01-13 10:42:20 PM'
I can of course globally search and replace the dates n the old data with a dummy date to make the import work but I don;t want to do this - the chronology of the forum would be lost and of course user info like birth dates etc. also lost, Can any one help? I don;t want to have to go through the 16ooo forum posts and 2000 users and change the date manually on each on......
Steve
October 26, 2009 at 12:49 am
cant u convert the date format something like this while importing?
Select Convert (datetime, '13/01/2008 22:42:20', 103)
I doubt if you will get AM/PM format for time in yyyy-mm-dd date format(lets see if someone has something better to offer)
---------------------------------------------------------------------------------
October 26, 2009 at 12:53 am
what does the 103 mean in the statement? And not sure I am making mysef clear...when I expoted the old data it basically dumped a massive sql file. Now I want to run this massive sql file for the import process - no?
I try and run the sql insert statements and it gives this error unless I convert the dates to the 'right' format.
"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."
Here is one example of the insert script:-
INSERT [tblAuthor] ([Author_ID], [Group_ID], [Username], [Real_name], [User_code], [Password], [Salt], [Author_email], [Homepage], [Location], [MSN], [Yahoo], [ICQ], [AIM], [Occupation], [Interests], [DOB], [Signature], [No_of_posts], [Join_date], [Avatar], [Avatar_title], [Last_visit], [Time_offset], [Time_offset_hours], [Date_format], [No_of_PM], [Show_email], [Attach_signature], [Active], [Rich_editor], [Reply_notify], [PM_notify], [Skype], [Login_attempt], [Banned], [Info]) VALUES (8, 4, 'Hookded', '', 'Hookded84C4571222', '541878451FDBB9F0B4089AAAE369E60413ED14B1', '5BAAA6A', 'bcccyyuu@gmail.com', '', 'United Kingdom', '', '', NULL, '', '', '', NULL, '', 6, '08/01/2007 17:04:00', '', NULL, '10/02/2007 00:15:49', '+', 0, 'dd/mm/yy', 0, 0, 1, 1, 1, 0, 0, '', 0, 0, '')
October 26, 2009 at 2:12 am
snadowitz (10/26/2009)
what does the 103 mean in the statement?
The character string that you are trying to convert is in this format dd/mm/yyyy (British/French) and its 3 if the input is dd/mm/yy
And not sure I am making mysef clear...when I expoted the old data it basically dumped a massive sql file. Now I want to run this massive sql file for the import process - no?
I guess you can still write T-SQL for coversions!
I try and run the sql insert statements and it gives this error unless I convert the dates to the 'right' format.
"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."
Converting your char strings in dd/mm/yyyy format to date should do well if you specify format as 103.
Thanks.
---------------------------------------------------------------------------------
October 26, 2009 at 3:50 am
forgive my extreme ignorance....so - before trying to import the data I run the command you specified then run the rest of the sql insert statements?
October 26, 2009 at 4:14 am
I actually meant converting the value in the insert statement (see the bolded part below)
INSERT [tblAuthor] ([Author_ID], [Group_ID], [Username],
[Real_name], [User_code], [Password], [Salt], [Author_email],
[Homepage], [Location], [MSN], [Yahoo], [ICQ], [AIM],
[Occupation], [Interests], [DOB], [Signature], [No_of_posts],
[Join_date], [Avatar], [Avatar_title], [Last_visit], [Time_offset],
[Time_offset_hours], [Date_format], [No_of_PM], [Show_email],
[Attach_signature], [Active], [Rich_editor], [Reply_notify],
[PM_notify], [Skype], [Login_attempt], [Banned], [Info])
VALUES (8, 4, 'Hookded', '', 'Hookded84C4571222',
'541878451FDBB9F0B4089AAAE369E60413ED14B1', '5BAAA6A',
'bcccyyuu@gmail.com', '', 'United Kingdom', '', '', NULL, '', '', '',
NULL, '', 6, CONVERT(datetime, '08/01/2007 17:04:00', 103),'',
NULL, CONVERT(datetime, '10/02/2007 00:15:49',103), '+',
0, 'dd/mm/yy', 0, 0, 1, 1, 1, 0, 0, '', 0, 0, '')
BTW how are you migrating your data? what destination you are selecting while exporting it?
---------------------------------------------------------------------------------
October 26, 2009 at 4:26 am
Ok - I think I see what your getting at......the data resides on a hosted database at an ISP called Webfusion. In their control panel I get the options to export the data and when i do this I get the large sql data file I mentioned earlier. The DB has only about 16 tables so I guess I could export the tables individually using the 'query' option in the DB admin panel and do the date conversion during the export itself...I am a novei so I don;t know what syntax I would use to convert the date. I know executing
'select * from tblAuthor'
Would give me all the rows in the table but what I want is for it to give me INSERT script and also do the date conversion - is this possible?
October 26, 2009 at 4:51 am
Ok, not sure if this is what you are looking for,
Test data
CREATE TABLE #T1 (id int, date datetime)
CREATE TABLE #T2 (id int, date varchar(100))
INSERT INTO #T2 VALUES (1,'13/01/2008 22:42:20')
INSERT INTO #T2 VALUES (2,'14/01/2008 22:42:20')
INSERT INTO #T2 VALUES (3,'15/01/2008 22:42:20')
INSERT INTO #T2 VALUES (4,'16/01/2008 22:42:20')
To insert into first table from second table and converting data,
INSERT INTO #T1
SELECT id,
convert(datetime, date, 103)
FROM #T2
I guess above query is what you need in your case.
Now check the data in #T1,
Select * from #T1
Hope this helps, please let me know if I went off the track and did not understand your requirement at all!
---------------------------------------------------------------------------------
October 26, 2009 at 4:57 am
yes - seems logical. i will give this a try and report back if it works - probably be tomorrow or the day after.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply