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.
| Author |
Topic |
|
eljapo4
Posting Yak Master
100 Posts |
 Posted - 2010-12-08 : 09:44:11
|
Hi, I'm currently doing the following:--1 Year Of DataDELETE FROM TableA WHERE CurrDate < (GETDATE() - 365)DELETE FROM TableB WHERE CurrDate < (GETDATE() - 365) The DBA alerted me that the field CurrDate is defined as nchar(35) instead of Datetime. Will the above delete code cause any issues? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-08 : 10:00:10
|
| Really.You need to see what the format of the date in the column is (and if it is consistent).Yes the above code could cause issues.It will convert the CurrDate value to a datetime using the connection settings. Will be likely to either be ok or fail but you might be unlucky.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
eljapo4
Posting Yak Master
100 Posts |
Posted - 2010-12-08 : 10:17:16
|
| The data in the CurrDate field is consistent and looks like this 'Feb 7 2010 12:00AM' what issues could I be faced with? I've been deleting data from a test DB with the above and it seems to have worked fine so far. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-08 : 11:04:26
|
| That should be ok because the character column will unambiguously and implicitely convert to a datetime in your delete.what you have isDELETE FROM TableA WHERE convert(datetime,CurrDate) < GETDATE()-365You have to be careful that no one comes along and notices the datatype mismatch and changes it toDELETE FROM TableA WHERE CurrDate < convert(varchar(30),GETDATE()-365)I would put in the explicit convert (of the CurrDate) to datetime to point out what you are doing.Note this won't use an index seek as it has to convert the column so might be slow.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-08 : 11:16:38
|
| "That should be ok because the character column will unambiguously and implicitely convert to a datetime in your delete."Provided the Language of the current user is English? or (I think) that the language implicitly divined from the Country of the connected user is English?I agree, explicitly convert the CurrDate colume to DATETIME forcing the conversion to use the style of the field.Better still get the column changed to Datetime datatype. Daft to be storing a Date/time value in CHAR datatype field |
|
|
|
eljapo4
Posting Yak Master
100 Posts |
Posted - 2010-12-08 : 11:26:59
|
| Nigel thank you for your advice on this |
|
|
|
eljapo4
Posting Yak Master
100 Posts |
Posted - 2010-12-08 : 11:33:40
|
| Well that's the next step of my investigation, i'm trying to track back to see why the value was set as nchar and not Datetime and hopefully from that change the field to be Datetime. |
|
|
|
|
|
|
Deleting data from Table depending on Datefield