October 28, 2009 at 6:34 am
Hi, I m trying to get the following result using getdate() :
2009-10-28 08:33:00.000 i.e: I want precision only in minutes and I don't want to display seconds..
Can anyone suggest me the convert function for this?
Thank you
October 28, 2009 at 6:43 am
select convert(datetime,convert(char,getdate(),100))
October 28, 2009 at 6:45 am
Awesome...Thank you for the very quick post.
October 28, 2009 at 6:47 am
I thought you didnt want seconds or miliseconds :ermm:
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 28, 2009 at 6:54 am
Does the value still need to be a datetime, if not then the following should help
select CONVERT(varchar(16), getdate(),120)
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 28, 2009 at 7:22 am
Maybe I misunderstood the OP, but I thought the expected output was a datetime with seconds and milliseconds stripped off, which is what my first post gives isn't it?
October 28, 2009 at 7:24 am
for me
select convert(datetime,convert(char,getdate(),100)) it worked perfect.I just don't want seconds and msecs.
October 28, 2009 at 1:40 pm
ssismaddy (10/28/2009)
for meselect convert(datetime,convert(char,getdate(),100)) it worked perfect.I just don't want seconds and msecs.
In that case I'd use
SELECT dateadd(mi, datediff(mi, 0, getdate()), 0)
It's supposed to perform better than converting from datetime to character back to datetime...
October 28, 2009 at 2:15 pm
I'd go with Lutz's suggestion.
Converting to character and back involves extra steps that don't need to be taken and tends to be slower over a large number of rows. Granted, if you're doing it to a single row the debate is mostly theoretical, but I find it's better not to use less efficient techniques just because you can.
The other option that is very close performance wise is(some would claim equivalent):
CAST(CAST(GETDATE()-.5 as int) as datetime)
It's a bit easier to remember than the dateadd/datediff for a lot of people and it still beats the character conversions.
I do seem to remember that there are slightly different variants of both of these methods that are supposed to overcome very specific problems though (using a higher precision decimal for the int conversion and a value other than 0 for the dateadd/datediff I believe), anyone want to touch on those?
October 28, 2009 at 2:53 pm
Garadin (10/28/2009)
I do seem to remember that there are slightly different variants of both of these methods that are supposed to overcome very specific problems though (using a higher precision decimal for the int conversion and a value other than 0 for the dateadd/datediff I believe), anyone want to touch on those?
I'd simply point to one of Lynns blog posts:
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
It has a summary of common date routines, like beginning of current/previous/next day/week/month.
October 28, 2009 at 3:06 pm
ssismaddy (10/28/2009)
Hi, I m trying to get the following result using getdate() :2009-10-28 08:33:00.000 i.e: I want precision only in minutes and I don't want to display seconds..
Can anyone suggest me the convert function for this?
Thank you
Check out this code.
select
getdate(),
dateadd(mi, datediff(mi, 0, getdate()), 0),
substring(convert(varchar(30),dateadd(mi, datediff(mi, 0, getdate()), 0),121),
1,
len(convert(varchar(30),dateadd(mi, datediff(mi, 0, getdate()), 0),121)) - 7)
October 28, 2009 at 3:07 pm
Lutz pointed you to Lynns great article .
One thing to keep in mind, especially with datetime, but it also goes for other datatypes:
Keep your data in the original datatype as much as possible !
For datetime, that means you'll have all datetime related functions available without datatype (implicite) conversion.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 28, 2009 at 3:13 pm
lmu92 (10/28/2009)
Garadin (10/28/2009)
I do seem to remember that there are slightly different variants of both of these methods that are supposed to overcome very specific problems though (using a higher precision decimal for the int conversion and a value other than 0 for the dateadd/datediff I believe), anyone want to touch on those?I'd simply point to one of Lynns blog posts:
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
It has a summary of common date routines, like beginning of current/previous/next day/week/month.
It's not the applied technique I was referring to, it was the very rare and specific issues that I seem to remember with these and the workarounds for them. Commonly when these methods are discussed someone comes in with a warning about specific instances where they'll fail.
October 29, 2009 at 4:26 am
Garadin (10/28/2009)
I do seem to remember that there are slightly different variants of both of these methods that are supposed to overcome very specific problems though (using a higher precision decimal for the int conversion and a value other than 0 for the dateadd/datediff I believe), anyone want to touch on those?
Yep, I'll take a shot at it 🙂
None of the methods are perfect - SQL Server really could do with a fast, native way to truncate/round dates and times (Oracle has had the TRUNC function for about eleventy million years I think).
DATEADD/DATEDIFF is a hack, moderately difficult to remember, and prone to overflows for smaller intervals.
The CONVERT to INT thing relies on some fairly obscure rounding behaviour and doesn't work at all with the numerous new date and time types and functions available from 2008 onward.
Converting to and from a fixed-length character string is also hackish, and relatively slow, although it is the only method of truncating date/time types documented in Books Online (under CONVERT).
A good CLR implementation will be pretty comprehensive and clean to call, but probably slower than all the T-SQL 'methods'.
So, absent an entirely satisfactory solution, a lot of it comes down to personal preference. Each method has advantages and disadvantages, but none is compellingly better than the others in all circumstances.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply