Forum

HomeHomeCoreCoreGeneralGeneralImporting from XLSImporting from XLS
Previous
 
Next
New Post
9/23/2015 9:00 AM
 

The spreadsheet I'm trying to import has two columns [PUB Year] and [PUB Month] and I'm trying to import them into a datetime field, moving the date to the last day of the month at the same time.

In the select I tried :

SELECT [ISBN] 
	,[Title]
	,[PUB Year] 
	,[PUB Month]
	,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,convert(datetime,concat('20',[PUB Year],'-',[PUB Month],'-01')))+1,0)) as 'PubDate' 
FROM [Book Sales Report$] order by ISBN;

and saw this error "Undefined function 'convert' in expression."

so I then tried cast:

SELECT [ISBN]
	,[Title]
 	,[PUB Year]
	,[PUB Month]
	,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,cast(concat('20',[Pub Year],'-',[pub month],'-01') as datetime))+1,0)) as 'PubDate'
FROM [Book Sales Report$] order by ISBN;

but got "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." returned

Could someone point me in the right direction to solve this please?

Many thanks

Barry

Previous
 
Next
HomeHomeCoreCoreGeneralGeneralImporting from XLSImporting from XLS


 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • 410.327.0007 • info@R2Integrated.com

Bookmark & Share Bookmark and Share