none
Using datediff to match the same date in different months and years

Ответы

Все ответы

  • Use datepart(day, DateCol) = 6

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Предложено в качестве ответа Murali_CHN 8 июня 2012 г. 5:23
    • Помечено в качестве ответа Kalman TothModerator 8 июня 2012 г. 13:29
  • For example i want to get the 'somevalues' where the date are

    ...., 06 Dec 2011, 06 Jan 2012, 06 Feb 2012, etc....

    how to do it with DATEDIFF??

    Do this check
    if substring(CONVERT(varchar,date1,112),7,2)=substring(CONVERT(varchar,date2,112),7,2)
    ...

    Many Thanks & Best Regards, Hua Min

    • Помечено в качестве ответа Nurkartiko 8 июня 2012 г. 7:37
    • Снята пометка об ответе Kalman TothModerator 8 июня 2012 г. 13:29
  • Thanks all...

    After read all replies, I change my mind and not using datediff again for this problem.

    i use solution from Hua Min

    Best Regards

  • Why do you need a more complex solution when you can use a single function (DAY or DATEPART) to get the day of the date?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    8 июня 2012 г. 12:23
  • Thanks Naomi,

    This is my original query

    select distinct
        2,
        txttable.Id, 
        txttable.GroupId,
        txttable.PublicationDate, 
        txttable.PageId, 
        p.Name,
        txttable._title,
        0
    from txttable, pagetable P
    where txttable.pageid = p.id
    	AND contains((txttable.txt, txttable._keywordjoined, txttable._searchtext),'(FORMSOF (THESAURUS, NEGERI)) OR ("KORUP*")')
    	AND txttable.publicationid in (2,3,4,5,6,7,8,9,10,11,12,58,59)
    	AND year(txttable.publicationdate) = 2012 -- it is possible for user to select just the year
    	AND month(txttable.publicationdate) = 06 -- it is possible for user to select just the month
    	AND day(txttable.publicationdate) = 08 -- it is possible for user to select just the date
    	AND txttable.statusid <> 3
    	and p.statusid <> 3
    

    My table contains about 2.6 million records, and when I use that query it takes 16 minutes. And I think the problem is the "date" clause

    And after I change to below query, it takes 6 seconds

    select distinct
        2,
        txttable.Id, 
        txttable.GroupId,
        txttable.PublicationDate, 
        txttable.PageId, 
        p.Name,
        txttable._title,
        0
    from txttable, pagetable P
    where txttable.pageid = p.id
    	AND contains((txttable.txt, txttable._keywordjoined, txttable._searchtext),'(FORMSOF (THESAURUS, NEGERI)) OR ("KORUP*")')
    	AND txttable.publicationid in (2,3,4,5,6,7,8,9,10,11,12,58,59)
    	AND datediff(year, CONVERT(datetime, '20120708', 112), txttable.publicationdate) = 0
    	AND datediff(month, CONVERT(datetime, '20120608', 112), txttable.publicationdate) % 12 = 0
    	AND substring(CONVERT(varchar,CONVERT(datetime, '20120708', 112),112),7,2)=substring(CONVERT(varchar,txttable.publicationdate,112),7,2)
    	AND txttable.statusid <> 3
    	and p.statusid <> 3
    

    Any suggestions?

    Best Regards

    9 июня 2012 г. 16:54
  • If the second query gives the correct result, what's the problem?

    Is there an index on publication date? This index is only of any use of the year is included. Even better if month and day are well, of course. But then you also need to write the query in a suitable way.

    Since you include constants in the query, I assume that you are generating the query dynamically?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    9 июня 2012 г. 17:46
  • Thanks Naomi,

    This is my original query

    select distinct
        2,
        txttable.Id, 
        txttable.GroupId,
        txttable.PublicationDate, 
        txttable.PageId, 
        p.Name,
        txttable._title,
        0
    from txttable, pagetable P
    where txttable.pageid = p.id
    	AND contains((txttable.txt, txttable._keywordjoined, txttable._searchtext),'(FORMSOF (THESAURUS, NEGERI)) OR ("KORUP*")')
    	AND txttable.publicationid in (2,3,4,5,6,7,8,9,10,11,12,58,59)
    	AND year(txttable.publicationdate) = 2012 -- it is possible for user to select just the year
    	AND month(txttable.publicationdate) = 06 -- it is possible for user to select just the month
    	AND day(txttable.publicationdate) = 08 -- it is possible for user to select just the date
    	AND txttable.statusid <> 3
    	and p.statusid <> 3

    My table contains about 2.6 million records, and when I use that query it takes 16 minutes. And I think the problem is the "date" clause

    And after I change to below query, it takes 6 seconds

    select distinct
        2,
        txttable.Id, 
        txttable.GroupId,
        txttable.PublicationDate, 
        txttable.PageId, 
        p.Name,
        txttable._title,
        0
    from txttable, pagetable P
    where txttable.pageid = p.id
    	AND contains((txttable.txt, txttable._keywordjoined, txttable._searchtext),'(FORMSOF (THESAURUS, NEGERI)) OR ("KORUP*")')
    	AND txttable.publicationid in (2,3,4,5,6,7,8,9,10,11,12,58,59)
    	AND datediff(year, CONVERT(datetime, '20120708', 112), txttable.publicationdate) = 0
    	AND datediff(month, CONVERT(datetime, '20120608', 112), txttable.publicationdate) % 12 = 0
    	AND substring(CONVERT(varchar,CONVERT(datetime, '20120708', 112),112),7,2)=substring(CONVERT(varchar,txttable.publicationdate,112),7,2)
    	AND txttable.statusid <> 3
    	and p.statusid <> 3

    Any suggestions?

    Best Regards

    Hi Nurkartiko,

    Do you get big amount of records by 6 seconds? If yes, I think it is acceptable and no need to tune your script.


    Many Thanks & Best Regards, Hua Min

    10 июня 2012 г. 13:38