Prev: Split variable and exec stored procedure
Next: How to get records from Local Access Databse Using OPENROWSET Function
From: Russell Mangel on 6 Feb 2010 04:25
Hi,

I need to convert a DATETIME to the following CHAR/VARCHAR

Jul 76

The Following TSQL works, but maybe there is a cleaner way?

DECLARE @d DATETIME = '1776-07-04';
SELECT SUBSTRING(DATENAME(MONTH,@d), 1, 3)+' '+SUBSTRING(DATENAME(YEAR,@d),
3, 2);

Thanks

Russell Mangel
Las Vegas, NV




From: Erland Sommarskog on 6 Feb 2010 05:54
Russell Mangel (russell(a)tymer.net) writes:
> I need to convert a DATETIME to the following CHAR/VARCHAR
>
> Jul 76
>
> The Following TSQL works, but maybe there is a cleaner way?
>
> DECLARE @d DATETIME = '1776-07-04';
> SELECT SUBSTRING(DATENAME(MONTH,@d), 1, 3)+'
> '+SUBSTRING(DATENAME(YEAR,@d), 3, 2);

I think that is as clean as it can get. This is shorter:

select convert(char(4), @d) + substring(convert(varchar, @d), 10, 2)

But I'm not sure that I like it better. What you have will produce something
useful, no matter the language setting, but my short version produces
rubbish when I set the language to Japanese.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Peso on 9 Feb 2010 06:37
DECLARE @d DATETIME = '1776-07-04'

SELECT RIGHT(CONVERT(CHAR(9), @d, 6), 6)


//Peter

"Russell Mangel" <russell(a)tymer.net> wrote in message
news:#brYb5wpKHA.5588(a)TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I need to convert a DATETIME to the following CHAR/VARCHAR
>
> Jul 76
>
> The Following TSQL works, but maybe there is a cleaner way?
>
> DECLARE @d DATETIME = '1776-07-04';
> SELECT SUBSTRING(DATENAME(MONTH,@d), 1, 3)+'
> '+SUBSTRING(DATENAME(YEAR,@d), 3, 2);
>
> Thanks
>
> Russell Mangel
> Las Vegas, NV
>
>
>
>
 | 
Pages: 1
Prev: Split variable and exec stored procedure
Next: How to get records from Local Access Databse Using OPENROWSET Function