Date Evaluation Question

I have a DATE1 and a DATE2 field whose data looks like this:

20180101
20171231
20171228

I need to do some analysis whereby I'll be looking at DATE1 field and comparing it to DATE2 field to gather all records that have a DATE2 field "within 45 days" of the DATE1 field.

Is it better to leave the field in the coded format "YYYYMMDD" or is it better to convert the fields to a true "DATE" format and calculate using an actual DATE formatted field?

Thanks
CRXI
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
CRXI

It would be better to convert to 'real' dates, then you could use DATEDIFF to find the no of days between DATE1 and DATE2 and that could be used in the criteria to return the records you want.

SELECT [DATE1]
FROM [TABLE1]
WHERE DATEDIFF(DAY,CONVERT(DATETIME,[DATE1]),CONVERT(DATETIME,[DATE2]))<=45
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MIKESoftware Solutions ConsultantAuthor Commented:
Thanks..!!
0
Mark WillsTopic AdvisorCommented:
Dates as strings can be implicitly converted when in YYYYMMDD format and used in a date context.

So something like : select dateadd(d,45,'20180101') or abs(datediff(d,date1,date2)) <= 45

Even the example above doesnt need 'convert' in that scenario so long as YYYYMMDD
declare @strDate1 char(8) = '20180101'
declare @strDate2 char(8) = '20180216'

select datediff(d,@strDate1,@strDate2)    -- returns 46

Open in new window


So, either Will work, but, ALWAYS better to have dates as a DATE / DATETIME formal datatypes.
0
Mark WillsTopic AdvisorCommented:
Oh, the MAJOR advantage of converting to true date datatype is validation of the string data....

It might be possible to have a string as 20180041 but will not be a true date.

So, convert first inside an error handling routine, or at least check isdate()
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.