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
  • Last Modified:

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
Asked:
Rowdyone52
  • 4
  • 3
  • 2
  • +3
1 Solution
 
HilaireCommented:
Please try

select * from yourtable where convert(datetime, yournvarcharfield, 120) > dateadd(y, -5, getdate())
0
 
HilaireCommented:
or do you needs records older than 5 years ??


select * from yourtable where convert(datetime, yournvarcharfield, 120) < dateadd(y, -5, getdate())
0
 
Rowdyone52Author Commented:
I get a Arithmetic overflow error converting expression to data type datetime. error.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rowdyone52Author Commented:
there are some fields that are null, could that be a problem?
0
 
HilaireCommented:
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
 
dvsameerkumarCommented:
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
 
dvsameerkumarCommented:

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
 
doobdaveCommented:
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
 
Rowdyone52Author 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
 
HilaireCommented:
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
 
doobdaveCommented:
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
 
MannaraprayilCommented:
Please try this

SELECT * from yourtable where
DATEADD(YEAR, 5,nvarcharfield) > getdate()
0
 
PSSUserCommented:
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
 
PSSUserCommented:
Sorry, just thought of an easier way:
SELECT *
FROM YourTable
WHERE CONVERT(CHAR(19),DateAdd(y, -5, GetDate()),120)<= nvarcharfield
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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