Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 333

# Select code from myTable where cancelDate < 5 years from today

There is a date in a nvarcharfield in the format  '2004-09-21 00:00:00' .  How Can I determine if that date is less than 5 years ago in a select statement?
0
Rowdyone52
• 4
• 3
• 2
• +3
1 Solution

Commented:

select * from yourtable where convert(datetime, yournvarcharfield, 120) > dateadd(y, -5, getdate())
0

Commented:
or do you needs records older than 5 years ??

select * from yourtable where convert(datetime, yournvarcharfield, 120) < dateadd(y, -5, getdate())
0

Author Commented:
I get a Arithmetic overflow error converting expression to data type datetime. error.
0

Author Commented:
there are some fields that are null, could that be a problem?
0

Commented:
you must have some weird strings that can't be converted to dates

try
select * from yourtable where case when isdate(yournvarcharfield)  = 1 then convert(datetime, yournvarcharfield, 120) else getdate() end < dateadd(y, -5, getdate())

to find faulty records try

select  yournvarcharfield from yourtable where isdate(yournvarcharfield)  = 0
0

Commented:
Hi

Try this

select * from table where datediff(day,mydate,getdate())< 1825

this worked for me

1825 is number of days in 5 years

0

Commented:

Try this

select * from table where datediff(day,mydate,getdate())< 1825

In the query:
mydate is the column name of the date field in your table
1825 is the number of days in 5 years
day should remain as day itself...dont change it
Table should be the table name you are reffering to
0

Commented:
Just a comment on dvsameerkumar's idea:

Can you be sure that there will ALWAYS be 1825 days in 5 years? What about leap years, there may be one or two of those in a 5 year period.

I think years shuold be used in the datediff fnuction, that way it's up to SQL Server to determine how many days there will be in those 5 years.

Rowdyone52:

Why not make that column data type "datetime"? This would make date comparisons a lot easier, and would prevent anyone from inserting invalid values into that field...
0

Author Commented:
its not an option for me.  The data is managed by a different group.  I am just using a dts to copy the data and once I have it I am using it to do a nightly update into a piece of software.  I pretty much just have to use it as it is.
0

Commented:
Rowdyone did you try the syntax in my last post ?

select * from yourtable where case when isdate(yournvarcharfield)  = 1 then convert(datetime, yournvarcharfield, 120) else getdate() end < dateadd(y, -5, getdate())
0

Commented:
Ok.

In that case, you must first verify that all dates in that field conform to the format you specified.

If there are some NULL fields than you need to decide whether you want to omit all of those null records or include them in your query.

To omit them, something like this should work:

select * from yourtable
where yournvarcharfield is not null
AND convert(datetime, yournvarcharfield, 120) > dateadd(y, -5, getdate())

Let me know if this helps.

Regards,

David
0

Commented:

SELECT * from yourtable where
DATEADD(YEAR, 5,nvarcharfield) > getdate()
0

Commented:
Since the date is in descending order of scale (YMDHMS), you can use a simple string comparison:
SELECT *
FROM YourTable
WHERE CAST(DATEPART(YEAR,DateAdd(y, -5, GetDate())) as Char(4)) + '-' +
CAST(DATEPART(MONTH,DateAdd(y, -5, GetDate())) as Char(2)) + '-' +
CAST(DATEPART(DAY,DateAdd(y, -5, GetDate())) as Char(2)) + ' ' +
CAST(DATEPART(HOUR,DateAdd(y, -5, GetDate())) as Char(2)) + ':' +
CAST(DATEPART(MINUTE,DateAdd(y, -5, GetDate())) as Char(2)) + ':' +
CAST(DATEPART(SECOND,DateAdd(y, -5, GetDate())) as Char(2)) <= nvarcharfield
0

Commented:
Sorry, just thought of an easier way:
SELECT *
FROM YourTable
WHERE CONVERT(CHAR(19),DateAdd(y, -5, GetDate()),120)<= nvarcharfield
0

## Featured Post

• 4
• 3
• 2
• +3
Tackle projects and never again get stuck behind a technical roadblock.