Import datetime into SQL as null

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 2008 Forums

Transact-SQL (2008)

Import datetime into SQL as null

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-01-27 : 19:29:51

I am trying to import data into SQL server from mySQL, it appears that on one of the fields there is nulled date times, and because of this SQL server is having a fit, does any one know of a way that I can overcome this.

CREATE TABLE [dbo].[Agents](
[agent_login_id] [varchar](600) NULL,
[Title] [varchar](600) NULL,
[Supervisor_name] [varchar](600) NULL,
[Country] [varchar](600) NULL,
[Location] [varchar](600) NULL,
[Start_Date][datetime] NULL,
[effective_Start_day][datetime] NULL,
[effective_End_day][datetime] NULL
)
go
insert into [Agents]
select * from openquery([support_cases],'select agent_login_id, Title, Supervisor_name, Country, left(Location,3), Dept_Entry_Date, effective_Start_day, ifnull(EFFECTIVE_END_DAY,2) as EFFECTIVE_END_DAY from agents limit 500000;')
go

the error being produced is:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Sure I could change the
[effective_End_day][datetime] NULL
to:
[effective_End_day][varchar](600) NULL

but I need it to maintain as a date time.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 00:53:31

If you need to maintain the Effective_End_day as DATETIME, why are you using the IFNULL(EFFECTIVE_END_DAY,2)??? Instead pass a datetime value that abides by your business rule there. That would solve your problem.

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 03:25:54

quote:
Originally posted by 2revup

I am trying to import data into SQL server from mySQL, it appears that on one of the fields there is nulled date times, and because of this SQL server is having a fit, does any one know of a way that I can overcome this.

CREATE TABLE [dbo].[Agents](
[agent_login_id] [varchar](600) NULL,
[Title] [varchar](600) NULL,
[Supervisor_name] [varchar](600) NULL,
[Country] [varchar](600) NULL,
[Location] [varchar](600) NULL,
[Start_Date][datetime] NULL,
[effective_Start_day][datetime] NULL,
[effective_End_day][datetime] NULL
)
go
insert into [Agents]
select * from openquery([support_cases],'select agent_login_id, Title, Supervisor_name, Country, left(Location,3), Dept_Entry_Date, effective_Start_day, ifnull(EFFECTIVE_END_DAY,2) as EFFECTIVE_END_DAY from agents limit 500000;')
go

the error being produced is:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Sure I could change the
[effective_End_day][datetime] NULL
to:
[effective_End_day][varchar](600) NULL

but I need it to maintain as a date time.

SQL Server doesnt throw this error due to NULL values
It may be that you've some date values which are outside sql server limited (like say dates before 1753-01-01) or it may be that format in which date were sent are unambiguos which caused SQL Server to interpret it wrongly
see below post for more details
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

So if its former convert those dates to NULL value or change datatype in sql server to datetime2 which has higher range and precision
If its latter try to pass it in unambiguos format like ISO format as the article suggests

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources