Archives

Why Doesn’t T-SQL Have a Date Conversion or a AllTrim() Function?

In all versions before 2008, SQL Server only supported a DateTime data type, not separate Date or Time types. Therefore, every date field in M2M is actually DateTime, and this can be inconvenient for reporting. Your CEO typically doesn’t care what time an invoice was processed, only which day. Therefore, one typically uses one of the following conversions to derive only the date:

SELECT DATEADD (dd, 0, DATEDIFF (dd, 0, GETDATE ())) -- Actually produces 2009-09-03 00:00:00.000
 
SELECT CONVERT (CHAR (8), GETDATE (), 112) -- 20090903

They do, however, have functions to pull out the year, month, or day of the week. Why not a fully optimized function to extract the complete date?

Furthermore, most languages include a trim or alltrim function which trims spaces from both sides of a string. I’m really tired of using something like the following:

SELECT RTRIM (LTRIM (inmast.fpartno))

Once again, couldn’t Microsoft optimize this process with a built in function? It should be noted that after I wrote this I ran across Pinal Dave’s excellent article on the subject where he suggests making a simple custom function to achieve this.

Do any of you have similar gripes about SQL Server or T-SQL?

Related posts:

4 comments to Why Doesn’t T-SQL Have a Date Conversion or a AllTrim() Function?

  • David,
    I often wonder why language developers can’t come up with easier ways to do many of the most common tasks. For example, preparing mixed variables (numeric and character) and literals to be assigned to another variable. Could they not create a smarter parser? So many hours toying with “‘” or ‘”‘ or whatever. Then to keep it all together some require special end of line characters. What are they thinking? Perhaps they don’t use the languages they create?

  • For the first, I assume they basically said, “There are already three ways to do this – let’s not clutter it up any more.” You can do this via CONVERT as you posted, also with a combination of CAST and SUBSTRING/LEFT, and finally by stringing together the DATENAME and YEAR functions. Heck – there are probably other alternatives that I can’t think of too! That being said, I’ve never met a DBA/developer who didn’t carry around a bunch of date functions (and perhaps a table or two) in their toolbox!

    For the second, this was definitely a choice. There is only two situations when you would need RTRIM(LTRIM()) – when you are using either a CHAR or a NCHAR datatype. You don’t need RTRIM(LTRIM()) with VARCHAR or NVARCHAR since the RTRIM() is done automatically.

  • Here’s a quick answer to your SQL date only issue:

    select CONVERT(varchar(10),getdate(),101) AS ‘DateOnly’

  • On the Trim issues, remember more than one character type can appear as white space. Don’t know what else you may be coding in, but this may b helpful to somebody out there.

    In programming, trim is a string manipulation function or algorithm. The most popular variants of the trim function strip only the beginning or end of the string. Typically named ltrim and rtrim respectively.

    This Javascript code trim implementation removes all leading and trailing occurrences of a set of characters specified. If no characters are specified it will trim whitespace characters from the beginning or end or both of the string.

    Without the second parameter, Javascript function will trim these characters:

    * ” ” (ASCII 32 (0×20)), an ordinary space.
    * “\t” (ASCII 9 (0×09)), a tab.
    * “\n” (ASCII 10 (0×0A)), a new line (line feed).
    * “\r” (ASCII 13 (0×0D)), a carriage return.
    * “″ (ASCII 0 (0×00)), the NUL-byte.
    * “\x0B” (ASCII 11 (0×0B)), a vertical tab.

    DEMO
    Source code for webtoolkit.trim.js

    /**
    *
    * Javascript trim, ltrim, rtrim
    * http://www.webtoolkit.info/
    *
    **/

    function trim(str, chars) {
    return ltrim(rtrim(str, chars), chars);
    }

    function ltrim(str, chars) {
    chars = chars || “\\s”;
    return str.replace(new RegExp(“^[” + chars + “]+”, “g”), “”);
    }

    function rtrim(str, chars) {
    chars = chars || “\\s”;
    return str.replace(new RegExp(“[” + chars + “]+$”, “g”), “”);
    }

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>