Brave Search

tsql : best way to cast variables to string type?

stackoverflow.com › questions › 1268762 › tsql-best-way-to-cast-variables-to-string-type

All-caps is literal text, lower-case is something into which you should interpolate a value:

CAST(expression AS CHAR)
-- or:
CAST(expression AS VARCHAR)

You can optionally specify a length.

Unfortunately, it's a bit harder with datetimes, if you want to format it in any way that's different from the default representation.

My info came from the MSDN site. I think you'll have to read that site, and others, carefully and play around with it a bit, but hopefully the CAST function will be a good start.

Good luck!

Answer from Platinum Azure on Stack Overflow

W3Schools

w3schools.com › sql › func_sqlserver_cast.asp

SQL Server CAST() Function

String Functions: ASCII CHAR_LENGTH ... BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION SQL Server Functions · String Functions: ASCII CHAR CHARINDEX CONCAT Concat ...

Stack Overflow

stackoverflow.com › questions › 1268762 › tsql-best-way-to-cast-variables-to-string-type

sql server 2005 - tsql : best way to cast variables to string type? - Stack Overflow

Top answer

1 of 3

29

All-caps is literal text, lower-case is something into which you should interpolate a value:

CAST(expression AS CHAR)
-- or:
CAST(expression AS VARCHAR)

You can optionally specify a length.

Unfortunately, it's a bit harder with datetimes, if you want to format it in any way that's different from the default representation.

My info came from the MSDN site. I think you'll have to read that site, and others, carefully and play around with it a bit, but hopefully the CAST function will be a good start.

Good luck!

2 of 3

1

you need to convert/cast it each time. I made a function to use:

CREATE FUNCTION QuoteNull
(
     @InputStr      varchar(8000)  --value to force to string
)
RETURNS
varchar(8000)
AS

BEGIN
    RETURN COALESCE(''''+@InputStr+'''','null')
END

it puts single quotes around the value or just the word null if it is null, but you can customize it as necessary.

here is a version that handles formatting dates automatically:

CREATE FUNCTION QuoteNull
(
     @InputStr      sql_variant   --value to force to string
)
RETURNS
varchar(8000)
AS

BEGIN
    DECLARE @String  varchar(8000)
    SET @String=COALESCE(''''+  CASE SQL_VARIANT_PROPERTY(@InputStr,'BaseType')
                                    WHEN 'datetime' THEN CONVERT(varchar(23),@InputStr,121)
                                    ELSE CONVERT(varchar(8000),@InputStr)
                                END
                             +'''','null')
    RETURN @String
END

Discussions

Can someone explain CAST() to me?

It's used to convert from one data type to another. Why do I sometimes need to use it (for timestamps mainly) and sometimes I dont It varies depending on the exact SQL version you're using, but sometimes certain types of implicit conversions can be made without explicit casting required. But in general it's good practice to cast even when you can get away with not doing so. More on reddit.com

r/SQL

9

6

March 22, 2024

Cast vs convert?

As a general rule, use CAST for as much as you can. It’s ANSI standard SQL so will work even if you move away from SQL Server. CONVERT on the other hand is SQL Server specific. More on reddit.com

r/SQL

18

3

March 29, 2024

How do I convert these values (currently String) into INT (but with a catch)

Cast(case when value like ‘%>%’ then 600 else value end) as int More on reddit.com

r/SQL

9

16

February 12, 2022

How do I parse a String into java.sql.Date format?

Can't your SQL server accept strings for dates? The ones I've used can, so I just do something like:

String sqlDate = new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat("dd-MM-yyyy").parse(startDate));

... and pass sqlDate to the parametrized query. Like konrad mentioned, lowercase 'mm' is for minutes, and uppercase 'MM' is for month, so I think that's where your problem was.

Videos