Post reply

Format

  • December 28, 2007 at 1:59 pm

    #62936

    How can i check the format of a field where it shud contain values starting with year like

    YYYY-23987

    YYYY-56567

    i want to check if that field is in the same format?

  • December 28, 2007 at 3:15 pm

    #763450

    This should do it, Mike...

    SELECT *

    FROM yourtable

    WHERE ISDATE(LEFT(yourcolumn,4))=1

    AND yourcolumn LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 28, 2007 at 10:48 pm

    #763490

    Mike Levan (12/28/2007)

    How can i check the format of a field where it shud contain values starting with year like

    YYYY-23987

    YYYY-56567

    i want to check if that field is in the same format?

    Are you storing dates in this format?

    With this format, there is high chance of having invalid dates

    like

    0123-99999

    0000-00000

    Madhivanan

    Failing to plan is Planning to fail

  • December 29, 2007 at 8:06 am

    #763505

    Heh... I'm thinkin' that why Mike as how to check the column 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 2, 2008 at 7:05 am

    #764024

    Jeff..cud u pls elaborate your method, i didnt follow wht ur doing there?

    MAdhavanan.. its not a date am storing but its a number which starts with 4 digit year and required number.

  • January 2, 2008 at 7:12 am

    #764028

    Mike Levan (1/2/2008)

    Jeff..cud u pls elaborate your method, i didnt follow wht ur doing there?

    MAdhavanan.. its not a date am storing but its a number which starts with 4 digit year and required number.

    Jeff's code checks whether your column has proper Year part , exactly 5 digits after Year part and 9 digits in total

    Madhivanan

    Failing to plan is Planning to fail

  • January 2, 2008 at 7:15 am

    #764031

    It just checks that your values are in the format of nnnn-nnnnn and the first 4 are a viable year. Please see "Like comparisons, Pattern Matching in Search Conditions" in Books Online for additional details... they're similar to RegEx but not nearly as powerful.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 2, 2008 at 7:46 am

    #764043

    thanks jeff.

    How can i enter time in tenths of an hour or has to check if the time entered is in tenths of an hour

  • January 2, 2008 at 1:47 pm

    #764187

    Jeff Moden (12/28/2007)

    This should do it, Mike...

    SELECT *

    FROM yourtable

    WHERE ISDATE(LEFT(yourcolumn,4))=1

    AND yourcolumn LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    Jeff,

    Would this have been faster

    SELECT *

    FROM yourtable

    WHERE yourcolumn LIKE '[12][089][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    AND ISDATE(LEFT(yourcolumn,4))=1

    i.e. Restrict the 1st 2 columns in the like to possible values (since there are realistically only 2 values for the first column and 3 for the second) and then only if that is true test the ISDATE for correct combinations (18,19 and 20).

    Also would the LIKE use an index in this case or would it have to do a table scan?

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • January 2, 2008 at 7:44 pm

    #764238

    Mike Levan (1/2/2008)

    thanks jeff.

    How can i enter time in tenths of an hour or has to check if the time entered is in tenths of an hour

    Instead of adding "1" for a whole day, you would simply add one tenth of an hour as as 6 minutes... DATEADD(mm,6,0)... the "0" represents '19000101" which has a date serial number of zero in SQL Server.

    If the date columns you're working with aren't limited to 6 minute (tenth of an hour) increments, the forumula for the "conversion" is quite simple as shown in this GETDATE() example...

    SELECT DATEADD(mi,DATEDIFF(mi,0,GETDATE())/6*6,0)

    ... again, the "0" is shorthand for "19000101" at midnight. Since the DATEDIFF function returns an Integer answer, dividing by 6 (1 tenth of an hour's worth of minutes) and then multiplying by 6 preserves the Integer number of minutes in 6 minute increments. When the DATEADD hits those incremental numbers, the minutes are returned in the full datetime as 0, 6, 12, 18, 24... etc...

    Then, a simple comparison against the "time entered" for the same column (substituting the column name for GETDATE() in the formula) would do the trick.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 2, 2008 at 8:17 pm

    #764242

    Kenneth Fisher (1/2/2008)

    Jeff Moden (12/28/2007)

    This should do it, Mike...

    SELECT *

    FROM yourtable

    WHERE ISDATE(LEFT(yourcolumn,4))=1

    AND yourcolumn LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    Jeff,

    Would this have been faster

    SELECT *

    FROM yourtable

    WHERE yourcolumn LIKE '[12][089][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    AND ISDATE(LEFT(yourcolumn,4))=1

    i.e. Restrict the 1st 2 columns in the like to possible values (since there are realistically only 2 values for the first column and 3 for the second) and then only if that is true test the ISDATE for correct combinations (18,19 and 20).

    Also would the LIKE use an index in this case or would it have to do a table scan?

    Kenneth

    The extra constraining you've done will slow the whole process down by an average of 60 to 80 milliseconds.... on a MILLION rows! So, I'd have to say it's pretty much a wash and whichever way you think is more readable for the next guy (or yourself) is the way to go.

    Here's the test data I used... very homogenous data...

    CREATE TABLE #TestFormat

    (

    RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    Data CHAR(10)

    )

    INSERT INTO #TestFormat

    (Data)

    SELECT TOP 1000000

    '1999-56567'

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    ... and here's the test code...

    --=======================================================================

    -- Run the following test more than once

    --=======================================================================

    SET NOCOUNT ON

    SET STATISTICS TIME ON

    DECLARE @Bitbucket CHAR(10)

    PRINT '===== "Flat" Like method ======================================'

    SELECT @Bitbucket = Data

    FROM #TestFormat

    WHERE Data LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    AND ISDATE(LEFT(Data,4))=1

    PRINT '===== Custom Like method ======================================'

    SELECT @Bitbucket = Data

    FROM #TestFormat

    WHERE Data LIKE '[12][089][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    AND ISDATE(LEFT(Data,4))=1

    When I run each section on a standalone basis, the "Flat" like usually wins by about 150 milliseconds... again, no big deal because this is on a million rows... I'd go for whichever you thought would be more clear to the next guy that has to read the code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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