Post reply

Replace Function in SQL 2005 and 2008

  • February 8, 2010 at 7:39 am

    #156653

    try this in sql server 2005

    SELECT REPLACE(CONVERT(CHAR(10),GETDATE(),112),'/','')+'.'

    returns 20100208.

    now run it on sql 2008

    returns 20100208 .

    anyone got any idea why?

    --
    Thiago Dantas
    @DantHimself

  • February 8, 2010 at 8:13 am

    #1115518

    Looks to me that SQL Server 2008 is doing it correctly. Your date is declared as CHAR(10), and the period is being appended at the end of the 10 characters.

    If you change the CHAR(10) to VARCHAR(10) you get what I'd expect in SQL Server 2008.

  • February 8, 2010 at 9:55 am

    #1115623

    yes, but REPLACE should convert it to nvarchar data-type and get rid of the extra spaces, right?

    --
    Thiago Dantas
    @DantHimself

  • February 8, 2010 at 10:10 am

    #1115633

    isn't the REPLACE function overloaded, so there are actually multiple "replace" functions that are used based on the parameters passed?

    I'm sure the same thing was discussed on the COALESCE statment, and why you can COALESCE chars/decimal/integer, because it's actually multipel functions, with different implementations, based on the input values.

    ie:

    PUBLIC FUNCTION REPLACE(val as VARCHAR) RETURNS VARCHAR...

    PUBLIC FUNCTION REPLACE(val as CHAR) RETURNS CHAR...

    PUBLIC FUNCTION REPLACE(val as NVARCHAR) RETURNS NVARCHAR...

    PUBLIC FUNCTION REPLACE(val as NCHAR) RETURNS NCHAR...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 8, 2010 at 10:27 am

    #1115649

    that is what i was thinking, but they must have changed it from 2005 to 2008 and im pissed that microsoft doesnt even mention it anywhere

    --
    Thiago Dantas
    @DantHimself

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply