1

Topic: Extraction of the data by date from an exterior file

Prompt, please, there is a file C:/date.txt, only one date, for example 02-08-2018 there is stored
As to me in sql to select that data, which more than this date
If I so register request

SELECT Dt, CustomerName, ItemRelation, SaleCount,
DocumentNum, DocumentYear, IsPromo
FROM [Action].dbo. mytab1 WHERE Dt> C:/date.txt

The answer
the Error of conversion of date or time from character string.
How to me to make a condition> C:/date.txt, instead of> 02-08-2018?

2

Re: Extraction of the data by date from an exterior file

Kontox wrote:

As to me to make a condition> C:/date.txt, instead of> 02-08-2018?

   smile

3

Re: Extraction of the data by date from an exterior file

Kontox;
Off: you hurried with a post, today only Thursday
PS. The file where lies, on the server or at you is local by the machine?

4

Re: Extraction of the data by date from an exterior file

iiyama, the file lies on the server.

5

Re: Extraction of the data by date from an exterior file

Kontox;
Date from a file can be received, for example, so:

declare @data varchar (20)
select @data=left (BulkColumn, 20) from OPENROWSET (BULK ' c:\temp\data.txt ', SINGLE_CLOB) AS x
select @data

Only date in a file is better for having in format YYYYMMDD, differently implicit conversion produces to you or an error, or not that month and day)

6

Re: Extraction of the data by date from an exterior file

And as to me then to make a condition>

declare @data varchar (20)
select @data=left (BulkColumn, 20) from OPENROWSET (BULK ' data.txt ', SINGLE_CLOB) AS x
select @data
SELECT Dt, CustomerName, ItemRelation, SaleCount,
DocumentNum, DocumentYear, IsPromo
FROM [Action].dbo. mytab1 WHERE Dt> @data

In that case I receive this error
The message 242, level 16, state 3, line 9
Type conversion of the data varchar in data type datetime led to a value output for range limits.

7

Re: Extraction of the data by date from an exterior file

Kontox wrote:

in that case I receive this error
The message 242, level 16, state 3, line 9
Type conversion of the data varchar in data type datetime led to a value output for range limits.

I about it notified already

komrad wrote:

only date in a file is better for having in format YYYYMMDD, differently implicit conversion produces to you or an error , or not that month and day

8

Re: Extraction of the data by date from an exterior file

Kontox;
Here so try

declare @data varchar (20)
select @data=left (BulkColumn, 20) from OPENROWSET (BULK ' data.txt ', SINGLE_CLOB) AS x
/*prepare date*/
select @data [original]
set @data=replace (@data, ' - ', '. ')
set @data=PARSENAME (@data, 1) +PARSENAME (@data, 2) +PARSENAME (@data, 3)
select @data [formatted]
SELECT Dt, CustomerName, ItemRelation, SaleCount,
DocumentNum, DocumentYear, IsPromo
FROM [Action].dbo. mytab1 WHERE Dt> @data