I am very new SQL. In my office i need to calculate the following from the main Server and display it to the client PCs. 1. Display the No. of Block ( 1 to 96) Every hour is divided into 15min blocks, 96 blocks in a day. 2. Display the block time as say 03:00 to 03:15 at say 3:10 hrs 3. Calculate the time left(in mm:sec format ) from the current time Please help. Thanks in advance.
There is no reason whatsoever to do it in T-SQL as the front ends should do this kind of work. However, I can give you a sample in T-SQL if you want. The idea is that you need to know the current time by querying getDate() and the number of seconds from midnight today so you can calculate your block number by dividing the seconds from midnight by 900, which is the number of seconds in a single 15 minute block. Adding the block number multiplied by 15 will give you beginning of the currect range and adding the next block number multiplied by 15 will give you the end of the current range. Converting with style 8 gives you the hh:mm:ss format so taking the first 5 characters of it will let you format your range and finally, if you use end of the range minus the value of the current time, it will give you the time left in the block. Here is the script: declare @now datetime; declare @midnight datetime; declare @secondsFromMidnight int; declare @blockNumber int; declare @blockRange varchar(15); declare @timeLeft varchar(8); select @now = getdate(), @midnight = dateadd(day, datediff(day, 0, @now), 0), @secondsFromMidnight = datediff(second, @midnight, @now), @blockNumber = (@secondsFromMidnight / 900) + 1, @blockrange = convert(varchar(5), dateadd(minute, (@blockNumber - 1) * 15, @midnight), 8) + ' to ' + convert(varchar(5), dateadd(minute, @blockNumber * 15, @midnight), 8), @timeLeft = right(convert(varchar(8), dateadd(minute, @blockNumber * 15, @midnight) - @now, 8), 5); select @blockNumber BlockNumber, @blockRange BlockRange, @timeLeft TimeLeft; -- results (executed at about 2:46 PM) BlockNumber BlockRange TimeLeft ----------- --------------- -------- 60 14:45 to 15:00 13:42 Again, there is no reason to do any of this nonsense in T-SQL, it is far better to use the front end code for formatting. Oleg