MSSQL - Convert speсial types to strings or HTML:MONEY to string, DATETIME to string, GUID or UNIQUEIDENTIFIER to string, VARBINARY or BINARY to string, XML to string, TIMESTAMP to string

Most of datatypes is relative simple convert to a string.
For this we can use the CONVERT() or even the CAST() function:

SELECT int_as_string = CONVERT(VARCHAR(20), int_field) FROM MyTable;

SELECT int_as_string = CAST(int_field AS VARCHAR(20))  FROM MyTable;

But for some other datatypes convert is not so simple.
For example for MONEY, DATETIME, UNIQUEIDENTIFIER, BINARY, XML and TIMESTAMP datatypes.

There the script which shows how to convert such datatypes to strings:

USE tempdb;
GO

-- Drop test table if exists
IF OBJECT_ID('test_converts') IS NOT NULL
    DROP TABLE test_converts;
GO

-- Create test table
-- The technique to generate random money value I have used from there:
--     http://yabele.blogspot.de/2013/08/mssql-random-number-generator-with.html
CREATE TABLE test_converts
(
    mon MONEY             NOT NULL DEFAULT RAND(CHECKSUM(NEWID())) * 1000,
    dt DATETIME           NOT NULL DEFAULT GETDATE(),
    guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    bin VARBINARY(MAX)    NOT NULL DEFAULT NEWID(),
    x XML                 NOT NULL,
    ts TIMESTAMP
);
GO

-- Fill test table with 3 records
-- For all columns except "x" (xml) the default values will be used
INSERT INTO test_converts (x)
VALUES
    ('<a b="c"><d/></a>'),
    ('<e f="g"><h/></e>'),
    ('<i j="k"><l/></i>');

-- Show original values
SELECT * FROM test_converts;

-- Convert values to string
SELECT
                          -- Third parameter (0) is default and generates result text
                          -- in "0.00" format
    money_as_string     = CONVERT(VARCHAR(20), mon, 0),
                          -- Third parameter (121) generates result text
                          -- in ODBC-canonical format: "yyyy-MM-dd HH:mm:ss.sss"
    datetime_as_string  = CONVERT(VARCHAR(30), dt, 121),
    guid_as_string      = CONVERT(VARCHAR(36), guid),
                          -- Third parameter (1) generates result text
                          -- in uppercase form with "0x" prefix
    binary_as_string    = CONVERT(VARCHAR(MAX), bin, 1),
    xml_as_string       = CONVERT(VARCHAR(MAX), x),
    timestamp_as_string = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ts), 1)
FROM
    test_converts;

After execution in SSMS (SQL Server Management Studio) we will have these two resultsets:



These techniques could be used for example to convert table-content to HTML.
In case you need convert values to HTML-string some converted string values will be necessary to HTML-encode:

-- Convert values to HTML-string
SELECT
                        -- Third parameter (0) is default and generates result text
                        -- in "0.00" format
    money_as_HTML     = CONVERT(VARCHAR(20), mon, 0),
                        -- Third parameter (121) generates result text
                        -- in ODBC-canonical format: "yyyy-MM-dd HH:mm:ss.sss"
    datetime_as_HTML  = CONVERT(VARCHAR(30), dt, 121),
    guid_as_HTML      = CONVERT(VARCHAR(36), guid),
                        -- Third parameter (1) generates result text
                        -- in uppercase form with "0x" prefix
    binary_as_HTML    = CONVERT(VARCHAR(MAX), bin, 1),
                        -- Convert XML-string to HTML-encoded string
    xml_as_HTML       = REPLACE(REPLACE(REPLACE(
                            CONVERT(VARCHAR(MAX), x)
                        , '&','&amp;'), '<', '&lt;'), '>', '&gt;'),
    timestamp_as_HTML = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ts), 1)
FROM
    test_converts;

Correspondent resultset in SSMS:

---

See also:
MSSQL - The random number generator with a random initialization (random seed)

MSSQL - The random number generator with a random initialization (random seed)

The random number generator function RAND() produces every time the same sequence of random numbers from 0.0000 to 1.0000.

You can initiate it with some number (seed value) and then RAND(SeedValue) will return a different sequence.
But for the same initialization value RAND() will generate the same sequence of random numbers.
To get a different sequence every time we need each time a different initialization value.
To do this, we can use a combination of NEWID() and CHECKSUM():

SELECT RND=RAND(CHECKSUM(NEWID()));

For example:

SELECT RND=RAND(CHECKSUM(NEWID())) FROM MyBigTable;

MSSQL - How to get rows in random sort order

Sometimes it is necessary to get the query result sorted in random order.

The first idea is to use a random number generator:

SELECT * FROM MyTable ORDER BY RAND();

But if we run this query several times, we can see that the rows are returned each time in the same order.
There is another function that returns something random - NEWID().
This function creates a unique value of type uniqueidentifier (GUID).

SELECT * FROM MyTable ORDER BY NEWID();

If you need to return a set of 100 random rows (Table Sample), you can do following:

SELECT TOP(100) * FROM MyTable ORDER BY NEWID();