Post reply

SELECT * WHERE pattern matches XXX

  • October 5, 2010 at 9:02 pm

    #387945

    Hi All,

    I haven't been able to find anything on google about this, and was just wondering if someone can help me.

    I've been tasked with upgrading a database that has a varchar field that is storing dates.

    I've solved the problem going forward, such that the program no requires you to enter valid dates, but all the old data could be anything at all.

    is there a why I can use an SELECT statement to either retrieve where field is not a valid date, or where it doesn't match say 'dd/mm/yyyy'

    Thanks in advance for any help you can offer.

    Regards

    Byron

  • October 5, 2010 at 9:28 pm

    #1232207

    This link might be helpful for you.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 5, 2010 at 10:00 pm

    #1232208

    wow, isDate is great. Thanks for taht.

    The only problem I have now is that it is returning false for all dates like this 30/07/2010. By the Server and my local pc which I'm running Management studio on are setup with dd/MM/yyyy date formats, but isDate still only seems to recognise mm/dd/yyyy.

    Anything I could check?

    Thanks again.

  • October 5, 2010 at 11:17 pm

    #1232238

    SET DATEFORMAT mdy;

    Raunak J

  • October 6, 2010 at 3:52 pm

    #1233163

    Hey, thanks guys this has been a great help.

    If I run the below statement just as a normal query it returns all the records I want it too, but if I run it on an open table (as I want to edit this records in management studio manually, Management studio crashes. Does anyone know why this might be?

    SET DATEFORMAT dmy;

    SELECT *

    FROM sgleComplaint

    WHERE isDate(dateReceived) = 0 AND dateReceived <> ''

    Thanks again

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply