June 21, 2011 at 1:25 pm
Hi,
I've been working this afternoon to split out the values of a datetime and pull out the minutes. However if the time is say 18:00:00 or 18:09:00 I only get returned 0 or 9 when I use DatePart(mi, @timeVariable)
Can anyone enlighten me as to how I can get the full value i.e. 00 or 09, this is driving me crackers, surely there has to be a way??
Thanks
Andrew
June 21, 2011 at 2:11 pm
Hi
This is not ideal but you could look to do the following
declare @date datetime
set @date='2011-06-21 20:03:01.450'
select cast( @date as varchar),substring(cast( @date as varchar),charindex(':',cast( @date as varchar))+1,2)
***The first step is always the hardest *******
June 21, 2011 at 2:13 pm
00 and 09 are character entries to sql, not digits.
Easiest way to display it is along the lines of:
SELECT RIGHT( '00' + DATEPART( mi, @timevariable), 2)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 21, 2011 at 2:30 pm
You probably shouldn't be doing this in T-SQL at all. This is a presentation issue and should be left to the presentation layer.
What are you ultimately trying to achieve? There might be a better way to approach this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 21, 2011 at 2:41 pm
Hi I managed to get around doing what I needed by splitting out the hour and minutes, checking the length and prepending a 0 where required. This is not ideal but it has enabled my query to run the way I needed it too.
I didn't need to do this for presentation it was for comparison against a varchar field. Basically I have a user input varchar of time e.g. 02:32:53 when a task is to run however I have a service which polls the database every minute but not every second, therefore the query needs to select all tasks within the minute. so therefore if the currenttime is 02:20:22 the service won't poll again till 02:21:22 so anything with a desire start time within 02:20:00 and 02:20:59 needs to be selected.
Thanks
June 21, 2011 at 3:54 pm
Andrew Westgarth (6/21/2011)
I didn't need to do this for presentation it was for comparison against a varchar field. Basically I have a user input varchar of time e.g. 02:32:53 when a task is to run however I have a service which polls the database every minute but not every second, therefore the query needs to select all tasks within the minute. so therefore if the currenttime is 02:20:22 the service won't poll again till 02:21:22 so anything with a desire start time within 02:20:00 and 02:20:59 needs to be selected.
Jeff Moden has several posts on this forum demonstrating that converting datetime data into varchar is very inefficient. I'm posting this from my cell phone, so it's too cumbersome to search for them right now.
This problem is much simpler to solve if you convert your varchar field to datetime rather than the other way. This has the added benefit that it's easier to validate the time entered.
There are two approaches that will work. One is to adjust the two fields so that they are on the same date and use the Datediff() function to if they're in the same minute. The second is to compare the hour and minute date parts of the two to see if they match. With this approach, you can let them enter times in 12 hour format and then use the mod operator on the hour.
I don't have access to SQL right now, so I can't provide any tested code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply