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 |
|
Claymationator
Starting Member
6 Posts |
 Posted - 2008-09-10 : 16:54:58
|
| I have an excel formula that I am using currently that I want to be able to convert to a SQL to add into a query that will be run. I am new to SQL, so forgive me if I am not too descriptive with my question. I also want to do it without having to use a CASE statement. The formula that I have is:=LOOKUP(MID(M2,FIND(":",M2)+1,2),{"0","15","30","45","60"},{0,15,30,45,45})It is looking at a time in a cell and returning the "Start Hour" in 15 minute increments. So, if the start time was 9:07, then it would return 9:00, if it was 9:17 it would return 9:15. Any help is appreciated. |
|
|
Claymationator
Starting Member
6 Posts |
Posted - 2008-09-10 : 16:56:28
|
| Sorry that I forgot to include this part, but I have received some help with the conversion, but it is quite long. Any ideas on how this would be simplified without the CASE statements? ,CASE WHEN DATEPART(n,CAST(OperationDateTime as datetime)) < '15' THEN '0' WHEN DATEPART(n,CAST(OperationDateTime as datetime)) < '30' THEN '15' WHEN DATEPART(n,CAST(OperationDateTime as datetime)) < '45' THEN '30' WHEN DATEPART(n,CAST(OperationDateTime as datetime)) < '60' THEN '45' ELSE DATEPART(n,CAST(OperationDateTime as datetime)) END AS [Room Start Hour] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 17:31:56
|
SELECT OperationDateTime,DATEADD(MINUTE, DATEDIFF(MINUTE, 0, OperationDateTime) / 15 * 15, 0)FROM Table1
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Claymationator
Starting Member
6 Posts |
Posted - 2008-09-10 : 17:44:43
|
| That works, thanks for the help. |
|
|
|
|
|
|
|
|
Converting Excel formula to SQL