CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jsullivan88
jsullivan88

Reputation: 43

Select datetime as varchar in sql server 2008

Is there any way to do a

select * from table

while casting or converting the datetimes to a varchar?

This seems like it would be easy but it completely eludes me, thanks.

Upvotes: 0

Views: 1623

Answers (2)

anon
anon

Reputation:

Maybe you could build a view that has all the converts, then select from the view instead of the base table. Not that I'm advocating SELECT * ...

You could build the view dynamically, too, from the catalog views.

EDIT adding sample to create a view, which will work as desired.

DECLARE @sql NVARCHAR(MAX) = N'CREATE VIEW dbo.View_table
AS
    SELECT';

SELECT @sql = @sql + CHAR(13) + CHAR(10) 
    + '     ' + QUOTENAME(name) + CASE 
        WHEN system_type_id IN (40,42,43,58,61)
        THEN ' = CONVERT(VARCHAR(10), '
    + QUOTENAME(name) + ', 120),' ELSE ',' END
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.table');

SELECT @sql = LEFT(@sql, LEN(@sql)-1) + '
    FROM dbo.table;';

PRINT @sql;
--EXEC sp_executesql @sql;

Not sure what you want to do about TIME data types, but that's not included because the same conversion won't work (it will just change the values to 1900-01-01).

Upvotes: 1

BlackTigerX
BlackTigerX

Reputation: 6146

You just need to specify the column name in the select

SELECT CONVERT(VARCHAR(10), [MyDateTimecolumn], 20),...

see this for other convert styles http://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 3

Related Questions