Filtering Text File by Date that is formatted YYYYMMDD to import into Sql Server table 2

[BxWill]

Using TSql to bulk insert a text file into a Sql Server 2012 transactional database.

Dates within text file are in the format YYYYMMDD.

I initially import all of data with data type of varchar into temporary table and then import into production table with a datetime2 data type.

Displayed below - I am testing the portion of the sql script that I will use to import the data from the temporary table into the production table.

Issue: I am not able to filter the data by Sale_Date.

For example, I want to import the data with a Sale_Date from January 1, 2016 to December 31, 2016 into Table "Sales_2016"

Using the sql script below, I receive records with a Sale_Dates of 1/01/2016, 1/01/2015, 1/2/2016, 1/5/2015...

It appears that the records are not being filtered properly.

Any insight as to what revisions I need to make to effectively filter the desired records?

Select 
Case
    When len(rtrim(ltrim([Sale_Date]) < > 8 then null
    When rtrim(ltrim([Sale_Date])) not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-3][0-9]' then null
    When isdate(rtrim(ltrim([Sale_Date]))) = 1 then convert(varchar(10), cast([Sale_Date] as datetime2),101)
else [Sale_Date]
from #TempTable
Where convert (varchar(10),cast ([Sale_Date] as datetime2),101) between '01/01/2016' and '12/31/2016'

 

[fredericofonseca]

If doing compares of dates do not convert to varchar. That is why its failing

you stated that the dates have format yyyymmdd - that is format 112
are there any cases where the date is not on that format? if so post here some of the examples of the different formats


If you are validating which records have a valid date then the following will probably be the most appropriate


select convert(date, Sale_date, 112) as Sale_Date
into #ValidDates
from #TempTable
where len(Sale_Date) = 8
and isdate(Sale_Date) = 1
and rtrim(ltrim(Sale_Date)) like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'

select Sale_Date
into #InValidDates
from #TempTable
where len(sale_date) <> 8
or isdate(sale_date) = 0
and rtrim(ltrim(Sale_Date)) not like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'


at this point table #validdates contain valid dates so can be compared directly to dates
Select Sale_Date
from #ValidDates
Where Sale_date between '2016-01-01' and '2016-12-31' -- this format should work regardless of the country/language settings

the records on #invaliddate are obviously values that did not meet the above criteria - in the case of mixed formats you would need to add those to the first query with different format type and eventually string manipulation


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[micang]

Hi,

You can use the 'isdate' function to check for valid dates

See if code below helps:

DECLARE @DateTable TABLE
(
  DateValue char(8)
)

insert into @DateTable(DateValue)
select '20170101'
union
select '2017101'
union
select '20170201'
union
select '20170231'
union
select '20170101'



select DateValue,isdate(DateValue) as ValidDate
from @DateTable
--where isdate(DateValue) = 1


--insert into yourtable (columnName)
select cast(DateValue as datetime2)
from @DateTable
where isdate(DateValue) = 1

Thanks
Michael

 

[BxWill]

When filtering or comparing/contrasting dates, are you indicating that I do not have to convert the date to the format MM/DD/YYYY?

Although the format of the date within the temporary table is varchar, are you stating that I can just simply modify the code to the following?

Select 
Case
    When len(rtrim(ltrim([Sale_Date]) < > 8 then null
    When rtrim(ltrim([Sale_Date])) not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-3][0-9]' then null
    When isdate(rtrim(ltrim([Sale_Date]))) <> 1 or rtrim(ltrim([Sale_Date])) not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-3][0-9]' then null
else [Sale_Date]
from #TempTable
Where Sale_date between '2016-01-01' and '2016-12-31'

If I take out the last line above and run the sql script, I receive all of the Sales dates and "Null" on the records with invalid dates.

Trying to understand why the creation of an additional table, #InvalidDates will assist in this case. My thinking is that for all records that have a "Null" in the field "Sale_Date" may have values in the other fields that will be of value. I do not want to eliminate the record just because it has a Null in the field "Sale_Date."

(Note, when I created the table via T-SQL, I specified something like CREATE ProductionTable ... Sale_Date datetime2 Null)

I will try my pre-existing sql script and modify the line with the WHERE clause using both scenarios below;

Option 1:
Where Sale_date between '2016-01-01' and '2016-12-31'

Option 2:
Where Sale_date between '20160101' and '20161231'


I guess that I made the assumption that the format of the date need to be changed from YYYYMMDD to MM/DD/YYYY for date comparison and filtering.

Note, the datatype of all of my fields in the temporary table is varchar(60). However, within my production table, all of the date fields have a format of datetime2.

Any additional insight is appreciated.

 

[fredericofonseca]

Comparing dates should always be with one of the following options
1 - as a date field
2 - as a int in the format yyyymmdd
3 - as a char in the format yyyymmdd - always 8 digits with optional separators (equal in all values and in the same position in all cases - requires leading zeros for year/month/day)

examples

-- case 1 - invalid compare format
select *
from 
(select '1/2/2016'
union all
select '1/3/1999'
union all
select '11/3/2999'
union all
select '12/31/2016'
) t(x)
order by x

-- case 2 - valid string compare case
select *
from 
(select '2016/02/01'
union all
select '1999/01/03'
union all
select '2999/11/03'
union all
select '2016/12/31'
) t(x)
order by x

-- case 3 - valid int compare case
select *
from 
(select 20160201
union all
select 19990103
union all
select 29991103
union all
select 20161231
) t(x)
order by x

this link will give some good insight on dates -

https://www.simple-talk.com/sql/t-s...et-sql-server-dates-and-times-horribly-wrong/

The samples I gave you were to help identify the good and the bad data - obviously there is more to it on your own side.
But you were asking about filtering by date range so what I gave you is what you were indirectly asking for.


As for how I would eventually do your conversion/validation

select case
       when  len(Sale_Date) = 8
        and isdate(Sale_Date) = 1
        and rtrim(ltrim(Sale_Date)) like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'
       then convert(date,  Sale_date, 112) as Sale_Date
       else cast(null as date)
       end as Sale_Date
     --.... all other fields you need
from #TempTable

Should you wish to filter the above query by a valid date then the correct way is

select *
from (select case
             when  len(Sale_Date) = 8
              and isdate(Sale_Date) = 1
              and rtrim(ltrim(Sale_Date)) like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'
             then convert(date,  Sale_date, 112) as Sale_Date
             else cast(null as date)
             end as Sale_Date
           --.... all other fields you need
      from #TempTable
     ) t
where sale_date between convert(date, '2016-01-01') and convert(date, '2016-12-31')

note that in this case you can use your format of choice as long as it recognized by SQL Server - but not advisable to do as you did.
Reasoning is that date conversion should always be specific as to the format used, and not left to be determined based on server/user locale settings

As another note I see your mention to datetime2.

I advise that you read this post, and also the links contained on it with regards to datetime2

https://www.sqlservercentral.com/Forums/1879073/Why-do-some-DBAs-avoid-DATETIME2

And my advise is that you do not use it unless you need the extra precision it offers or to use dates prior to 1753

with regards to your particular example it looks like field sale_date is only a date without time on it.
In that case I would also advise to define the field as date, not datetime/datetime2 as time portion will only confuse things and use more storage



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[BxWill]

Thanks for the great insight!

I will review and implement accordingly.