Sql-server – Convert datetime to europe default without time

date formatdatetimesql serversql-server-2008t-sql

I have a datetime field that I need to convert into the format DDMONYY (27Aug12). I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time. How can I easily convert a date to the format I need?

Best Answer

Instead of your hack with replace, why not just cast to the right data type? This doesn't need to be nvarchar and the only reason it contains time is because you included too many characters. You can of course apply your replace here as well (or you could do that at the client).

SELECT CONVERT(CHAR(11),GETDATE(),13)
UNION ALL
SELECT CONVERT(CHAR(11), DATEADD(DAY, 10, GETDATE()), 13);

Results:

27 Aug 2012
06 Sep 2012

Related Solutions

Convert a Unix timestamp to a DATETIME in a View

the result of the expression

to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND')

is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it is done in this example. This automatic conversion to the oracle stringtype VARCHAR2 is controlled by nls_date_format and some server settings. If you want your view to return a VARCHAR2 value with the calculated date in the format 'YYYY-MM-DD HH24:MI:SS' and not a DATE value you can do this by using a conversion function: expression

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND'),'YYYY-MM-DD HH24:MI:SS')

But in this case the return value of the view is a VARCHAR2 and not a DATE value.

SQL Server – Convert varchar Column to datetime

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME, 
    REPLACE(LEFT(varchar_col, 11), '/', ' ') 
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.

Best Answer

Related Solutions

Convert a Unix timestamp to a DATETIME in a View

SQL Server – Convert varchar Column to datetime

Related Question