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 |
|
rajayan2000
Starting Member
2 Posts |
 Posted - 2010-06-17 : 00:26:33
|
| Hi,I have a query in Oracle but unable to convert it in SQL server format."Select produser1,count(docname) as Folio_Count, round(sum(round(extract(hour from (PROD1END-PROD1START))*60+extract(minute from (PROD1END-PROD1START))+ extract(second from (PROD1END-PROD1START))/60,2))/60,2) as Time_Taken_hrs from tblworkinfo where PROD1END BETWEEN '<Date>' AND to_date('<Date>','dd-MON-yy')+1 group by produser1"How to get the result from above query when users inputs two date values and based on those two values i have to get the work status.Please help me. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 02:24:57
|
| Dates are always stored in an internal date format (provided you use the DATETIME datatype) so there is no need to use "to_date" (unless the dates are stored in varchar etc.!)For the difference between two dates:DATEDIFF(Day, MyEarlierDate, MyLaterDate)Instead of first parameter "Day" you can put most things that are obvious:Year, Quarter, Month, Day, Hour, Minute, Second, Millisecond ... and a bunch more (see SQL DOCs)To get part of the date useDATEPART(Day, MyDateColumn)same first parameter values as aboveTo get Minutes you may still want to use DATEDIFF to get the number of Seconds between the dates and then divide by 60 as DATEDIFF only returns whole units. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 02:27:27
|
| P.S. I looked the parameters up, just for completeness the additional ones are:dayofyear, week, weekday and in SQL2008: nanosecond (have to use DATETIME2 to store that), TZoffset, ISO_WEEK |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Date Functions in MS SQL Server