asked on

SQL script to find last 14 days of records

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'.

kevwit

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

kmol

ASKER

It's SQL server 2005, does not recognize str_to_date changed to simply str but get a unrecognized 2nd parameter in argument

kevwit

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())

kmol

ASKER

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"

kmol

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial