asked on

MS-SQL Compare Date Fields of Different Data Type

I have a Microsoft SQL database that imports a CSV file into a table daily. All of the fields in the database have a data type of nvarchar(max) and the fields in the CSV are all strings (enclosed in double quotes).

There is a field in the CSV file -- invoice_date -- that is the date that an item was created. It is in the format of mm/dd/yyyy, but single digit months and days do not have a leading zero.

In the database table, there is another field -- import_date -- that is the date that the CSV was imported and it is formatted as yyyy-mm-dd hh:mm:ss.ssss .

I need to be able to compare these fields against today's date and also against the date of ten days ago.

I need a query that does the following.

select *
from import_table
where import_date = cast(getdate() as date)
and invoice_date <is less than 10 days old>

I tried to convert and cast the invoice_date and the getdate() function in that last line, but it gets an error attempting to convert the string to a date. What is the easiest way to accomplish this?

Jose Torres

You said that the datatype of all the columns is nvarchar, therefore you need to convert the import_date and invoice_date before doing the comparison

Try the following and see if that gives you what your are looking for.

DECLARE @import_table TABLE (
	RecNo NVARCHAR(MAX),
	invoice_date NVARCHAR(MAX),
	import_date NVARCHAR(MAX) 
)
INSERT INTO @import_table VALUES 
('1','1/1/2016','2016-01-01 11:30:00.000'),
('2','3/14/2016','2016-03-24 11:30:00.000'),
('3','3/10/2016','2016-03-10 11:30:00.000'),
('4','3/13/2016','2016-03-13 11:30:00.000'),
('5','2/24/2016','2016-02-24 11:30:00.000'),
('6','3/24/2016','2016-03-24 11:30:00.000')

select *, cast(getdate() - 10 as date)
from @import_table
where CAST(import_date AS DATE) = cast(getdate() as date)
and CAST(invoice_date  AS DATE) >= cast(getdate() - 10 as date)

Open in new window

Brian Pringle

ASKER

I get the same result.

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Open in new window

ASKER CERTIFIED SOLUTION

Jose Torres

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Pringle

ASKER

That was the problem. I knew that I had the castings right!

There were four fields that have been causing other problems because the user inputted tabs into the actual user form, which breaks the input for the tab-delimited file.

Thank you!