INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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?

CODE

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' 

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

Hi,

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

See if code below helps:

CODE --> sql

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

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

(OP)
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?

CODE

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.

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

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

CODE

-- 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-sql-programming/...


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

CODE

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

CODE

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/Wh...

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
Thanks for the great insight!

I will review and implement accordingly.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close