?
Solved

SQL script to find last 14 days of records

Posted on 2009-02-23
8
Medium Priority
?
621 Views
Last Modified: 2012-06-21
I'm trying to return the last 14days of records.  The date field in the system is in char type format, looks like yyyymmdd0000.    I can not modify database, only run queries.

here's one example of what I 've tried
wHERE
ASCIdatetime < (Day, -14, GETDATE()) or ASCIdatetime = GetDate()
but get a date conversion error.
I tried converting it to different datetime format first but just get the new field as same information, example CONVERT(char(20),ascidatetime, 101) as 'Date'.

0
Comment
Question by:kmol
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:kevwit
ID: 23717711
Here is one way (if this from a mysql 4.1 or newer server):

For reference:
'http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_get-format


SELECT * FROM tbl_TableName
WHERE STR_TO_DATE(ASCIdatetime,'%Y%m%d%H%i%s') > DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY)

Open in new window

0
 

Author Comment

by:kmol
ID: 23729658
It's SQL server 2005, does not recognize str_to_date changed to simply str but get a unrecognized 2nd parameter in argument
0
 
LVL 1

Expert Comment

by:kevwit
ID: 23730189
Ok, sorry this is posted in MySQL section, but I now see the tag is SQL 2005.

1. Apply convert to your query to reformat the date (there has got to be better ways to do this but this is what I came up with:

convert(datetime, (mid(ASCIdatetime, 5, 2) + '/' + mid(ASCIdatetime, 7,2) + '/' + left(ASCIdatetime,4) + ' ' + mid(ASCIdatetime, 9, 2) + ':' + mid(ASCIdatetime, 11, 2)), 101)

2. Add before your SELECT statement (if it is a T-SQL query)
SET @StartDate = DATEADD([day], -14, now())

3.  Make your where statement:
WHERE
ASCIdatetime > @StartDate

or you could skip step 2 and do

WHERE
ASCIdatetime > DATEADD([day], -14, now())



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!

 

Author Comment

by:kmol
ID: 23737773
thanks kevwit...but still not working...
it doesn't recognize mid - I substituted substring
it doesn't recognize Now() - I substituted GetDate()
the conversion works but when add the where clause
WHERE
ASCIdatetime > DATEADD([day], -14, GetDate())
I still get the error "Conversion failed when converting datetime from character string"
0
 

Author Comment

by:kmol
ID: 23741047

This works
convert(datetime, (substring(ASCIdatetime, 5, 2) + '/' + substring(ASCIdatetime, 7,2) + '/' + left(ASCIdatetime,4) + ' ' + substring(ASCIdatetime, 9, 2) + ':' + substring(ASCIdatetime, 11, 2)), 101)

BUT WHEN i ADD THIS
WHERE ASCIdatetime > DATEADD([day], -14, GetDate())
"Conversion failed when converting datetime from character string"
I don't understand why it's not working.  
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24012301
this should bring you on track
WHERE ASCIdatetime < CONVERT( VARCHAR(8), DATEADD(Day, -14, GETDATE()), 112) 
   or ASCIdatetime >= CONVERT( VARCHAR(8), DATEADD(Day, -14, GETDATE()), 112) 

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question