date and Convert() - 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

General SQL Server Forums

New to SQL Server Programming

date and Convert()

Author

Topic

dbwork
Starting Member

7 Posts

Posted - 2006-09-29 : 13:46:46

Trying to making the move from MS Access to SQL 2000.

i'm importing a text file with a date format like:
06012006 -- always 10 char

and having mucho prob's

here's my code:
INSERT Auth_ICP
(
[ICP_ID],
[MemberID],
[MbrExtID],
[ICPMonth]
)
SELECT

[ICP_ID],
[MemberID],
[MbrExtID],
convert(datetime,SUBSTRING([ICPMonth],1,2) + "/" + SUBSTRING([ICPMonth],3,2) + "/" + SUBSTRING([ICPMonth],5,2))
FROM MN_Auth_ICP

currently i'm importing the data as is (varChar) then moving appending the data to a tbl with proper fields. Help, thanks in advance.

dbwork
Starting Member

7 Posts

Posted - 2006-09-29 : 13:50:44

By the way one of the problems could be Null values when appending.

dbwork
Starting Member

7 Posts

Posted - 2006-09-29 : 14:06:55

Thanks, but i get an error Msg:
Invalid column name '/'.

Maybe it's the null records in the table?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 14:10:03

Sorry my bad - I copied part of what you did without checking it

You must put strings in single quotes, not double quotes on SQL Server.

INSERT Auth_ICP 
(
[ICP_ID],
[MemberID],
[MbrExtID],
[ICPMonth]
)
SELECT 

[ICP_ID],
[MemberID],
[MbrExtID],
convert(datetime, SUBSTRING([ICPMonth],1,2) + '/' 
+ SUBSTRING([ICPMonth],3,2) + '/' + SUBSTRING([ICPMonth],5,4), 101)
FROM MN_Auth_ICP

dbwork
Starting Member

7 Posts

Posted - 2006-09-29 : 14:16:11

Very nice; that worked.

It seems like a lot of code for each date field, but we are up and running now.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-29 : 14:27:56

Assuming your input date format is MMDDYYYY, this code is simpler, and is also unambiguous for all SQL Server date format settings.


INSERT Auth_ICP 
	(
	[ICP_ID],
	[MemberID],
	[MbrExtID],
	[ICPMonth]
	)
SELECT 
	[ICP_ID],
	[MemberID],
	[MbrExtID],
	[ICPMonth] =
		convert(datetime,
		SUBSTRING([ICPMonth],5,4)+
		SUBSTRING([ICPMonth],1,4) )
FROM
	MN_Auth_ICP

CODO ERGO SUM

dbwork
Starting Member

7 Posts

Posted - 2006-09-29 : 15:23:56

OK, thanks again. i like the one with out the ambiguous integer value "101".

BTW this really save me a lot of time, single quote got me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources