Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    38

    Unanswered: the function to_days can't use in MSServer?

    hi,now i convert the mysql to sqlserver, the code below:
    Code:
    select count(*) from entryinfo  where  entryinfo.contractid = ? and To_DAYS(entryinfo.starttime)<=TO_DAYS(?) and TO_DAYS(?)<=TO_DAYS(entryinfo.finishtime);
    when run this,it display "to_days can't be identify"
    why?

    thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no such function in sql server

    you have two choices --

    1. come up with an expression that will calculate the equivalent of the TO_DAYS function (this is possible, but might take a fair bit of work)

    2. find another way to write your WHERE clause conditions, like this --
    Code:
    select count(*) 
      from entryinfo  
     where entryinfo.contractid = ? 
       and entryinfo.starttime <= ?
       and entryinfo.finishtime >= ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    38
    in sql server,no function can instead of 'TO_DAYS'?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's right

    you can write an expression to simulate it, if you really need to

    but why would you really want to? the actual day number itself cannot possibly mean anything

    for example, in the query you gave, you shouldn't be using TO_DAYS in the first place, it's very inefficient compared to the way i showed you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006
    Posts
    38
    well,i just want to compare the entryinfo.starttime with the ?(? is a parameter)
    the format of the entryinfo.starttime is yyyy-mm-dd
    so i want to change the yyyy-mm-dd to days or seconds
    in mysql,todays can do it

    TO_DAYS(date)

    Given a date date, returns a day number (the number of days since year 0).

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kingxiaobao
    so i want to change the yyyy-mm-dd to days or seconds
    actually, no -- no, you do not

    what you want is to find rows where the starttime is less than or equal to a given date and the finishtime is greater than or equal to that same date

    converting all three to a day number is only one way of doing this, and it is not the most efficient way

    the most efficient way is given in post #2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2006
    Posts
    38
    Um............shortly,i want to know
    How would I write this MySQL statement in MS SQL SERVER please?
    WHERE TO_DAYS(entryinfo.starttime) = TO_DAYS('1997-10-07')

    thank you!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One way would be:
    Code:
    -- some other SQL goes here
       WHERE entryinfo.starttime BETWEEN '1997-10-07' AND '1997-10-07 23:59:59.997'
    As Rudy pointed out in post #2 above, this isn't exactly the same as your original code, although it will do exactly the same thing, just many times faster.

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, pat, i'm not sure what i was doing wrong, but obviously my explanation wasn't working
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2012
    Posts
    1

    Question Please help me convert this my-sql query to sql am using MS-SQL 2005

    select emp.FirstName,emp.LastName, emp.CategoryCode, emp.WorkStatus, emp.Position,emp.DepartmentID,dept.DepartmentName, emp.LocationID,
    wl.Location,emp.NativeAddress,lh.*,to_days(getdate ()) - to_days(date_add(lh.enddate,interval 1 day)) as OverStayedDays
    from leavehistory as lh join employeemaster as emp on emp.employeenumber = lh.employeenumber left join department as dept
    on dept.departmentid = emp.departmentid left join worklocation as wl on wl.locationid = emp.locationid
    where (lh.datereported is null and date_add(lh.enddate,interval 1 day) < curdate()) and emp.WorkStatus = 'ON LEAVE' having OverStayedDays between 1 and 30 order by employeenumber

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's the basic idea, you might have to tweak the numbers...
    Code:
    SELECT emp.FirstName
         , emp.LastName
         , emp.CategoryCode
         , emp.WorkStatus
         , emp.Position
         , emp.DepartmentID
         , dept.DepartmentName
         , emp.LocationID
         , wl.Location
         , emp.NativeAddress
         , lh.*
         , DATEDIFF(DAY,lh.enddate,GETDATE()) - 1 AS OverStayedDays
      FROM leavehistory as lh 
    INNER
      JOIN employeemaster as emp 
        ON emp.employeenumber = lh.employeenumber 
       AND emp.WorkStatus = 'ON LEAVE' 
    LEFT OUTER
      JOIN department as dept
        ON dept.departmentid = emp.departmentid 
    LEFT OUTER
      JOIN worklocation as wl 
        ON wl.locationid = emp.locationid 
     WHERE lh.datereported IS NULL 
       AND lh.enddate < DATEADD(DAY,1,CURDATE()) 
       AND DATEDIFF(DAY,lh.enddate,GETDATE()) BETWEEN 2 AND 31 
    ORDER 
        BY employeenumber
    note that the last two lines markes in blue seem to be incompatible with each other
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •