SQL Text Date Problem!!!

[mbarnett]

Hi All,

Got an issue with SQL statement. My problem is I import a text file 4 times a day (data is always updated). The first import may contain 20 lines the next 50 always growing.

To avoid double counting (since the import always has the previous data) I tried to use the below SQL statementto delet any data from my table with the current date. Currently I have to go into the statement and change the date manually.

I tried using the Date() varaible but since my import date is text (18Feb05) it does not work...I tried changing the import from text to date, but I get conversion errors and the date disappears. Any suggestions would be greatly appreciated.


DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE tblAA_AA_New_DMO.Date="18Feb2005

 

[PaulBricker]

Generally, you can manipulate date datatypes using either the Format() function or the CDate() function. The format() function changes a date datatype to a string, and CDate() changes a string to a date datatype. So in your case, you might try
DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE Format(tblAA_AA_New_DMO.Date,"ddmmmyyyy")="18Feb2005"

Paul

 

[r937]

this should allow you to delete the rows for the current date without having to touch the query --

delete
  from tblAA_AA_New_DMO 
 where [Date]
     = format(date(),'dmmmyyyy')

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[mbarnett]

Thanks Paul for the reply,


tblAA_AA_New_DMO.Date is a string equal to 18Feb2005. Would I change my Sql statement to

DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE tblAA_AA_New_DMO.Date = Format(Date(),"ddmmmyyyy")

 

[mbarnett]

It worked thanks!!!!!!!!!!