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?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
Thanks..!!
0
 
Mark WillsConnect With a Mentor Topic 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 WillsConnect With a Mentor Topic 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.