Problem returning file list older than 3 days

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem returning file list older than 3 days

Author

Topic

dhjackal
Starting Member

42 Posts

Posted - 2009-01-26 : 06:29:17

I hope someone can help

I have a list of files produced from running a EXEC xp_cmdshell against a directory

The list looks like

06/01/2009 19:00 890,333,696 Database_backup_200901061900.bak
07/01/2009 19:00 891,382,272 Database_backup_200901071900.bak
08/01/2009 19:00 891,382,272 Database_backup_200901081900.bak
09/01/2009 19:00 892,430,848 Database_backup_200901091900.bak
10/01/2009 19:00 892,430,848 Database_backup_200901101900.bak

I want to reverse the file list and return only the files older than 3 days old e.g.

SELECT REVERSE( SUBSTRING( REVERSE(Filename), 0, CHARINDEX(' ', REVERSE(Filename) ) ) ),
SUBSTRING(Filename, 1, 22)
FROM #tmpFiles
WHERE DATEDIFF( dd, CONVERT(DATETIME, LEFT( Filename, 20) ), GETDATE() ) > 3

This works on one SQL Server instance but not another.

I get the following error message

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The #tmpFiles table is created with the following statement

CREATE TABLE #tmpFiles
(FileName varchar(150) )

Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-26 : 07:07:57

Maybe SET DATEFORMAT is your friend?
http://msdn.microsoft.com/en-us/library/aa259188(SQL.80).aspx

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

dhjackal
Starting Member

42 Posts

Posted - 2009-01-26 : 07:25:58

Brilliant - Thanks webfred.

I was pulling my hair out there but you've saved my bacon.

Thanks again

dhjackal
Starting Member

42 Posts

Posted - 2009-01-26 : 07:40:04

Any idea why this would be different between servers? Both are set up with a Language of British (United States). The Server Collation is differnt but only in terms of

Latin1_General_CI_AS v SQL_Latin1_General_CI_AI (Accent insentitive). Having to set this doesn't make my script very portable. Is there a way I can check the current setting so that I can toggle between DMY or MDY as necessary?

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:58:52

always its better to use universal format yyyy-mm-dd hh:mm:ss to avoid this type of errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources