Date Conversion

  • Hi,

    I would like the results of this simple query to show just the 'Created Date' in dd/mm/yyyy format.

    select name as 'Database Name', crdate as 'Created Date', filename as 'MDF Location' from dbo.sysdatabases

    master2003-04-08 09:13:36.390D:\MSSQLSERVER\MSSQL.8\MSSQL\DATA\master.mdf

    tempdb2010-05-20 19:55:15.900D:\MSSQLSERVER\MSSQL.8\MSSQL\DATA\tempdb.mdf

    model2003-04-08 09:13:36.390D:\MSSQLSERVER\MSSQL.8\MSSQL\DATA\model.mdf

    msdb2005-10-14 01:54:05.240D:\MSSQLSERVER\MSSQL.8\MSSQL\DATA\MSDBData.mdf

    What's the best way of achieving this?

    Many Thanks.

  • select name as 'Database Name',CONVERT(char(10), crdate, 103) as 'Created Date', filename as 'MDF Location' from dbo.sysdatabases

  • Cheers Steve. Easy when you know how!

    Thanks

  • One last thing... If I wanted to Order by 'Created Date' how would this be achieved? I presume the datatype will need to be changed?

  • You would order by the column alias - in this case, "Created Date".

    ORDER BY [Created Date]

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thanks Wayne, I should have mentioned that when I run the query below.

    select name as 'Database Name',CONVERT(varchar(10), crdate, 103) as 'Created Date', filename as 'MDF Location' from dbo.sysdatabases

    Order by 'Created Date'

    I get the following result set. I would like it to be in descending date order.

    Server1\INST1db101/05/2009D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db1.mdf

    Server1\INST1db202/10/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db2.mdf

    Server1\INST1db303/09/2009D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db3.mdf

    Server1\INST1db407/08/2009D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db4.mdf

    Server1\INST1db507/11/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db5.mdf

    Server1\INST1db608/04/2003D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db6.mdf

    Server1\INST1db708/04/2003D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db7.mdf

    Server1\INST1db809/06/2009D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db8.mdf

    Server1\INST1db909/07/2009D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db9.mdf

    Server1\INST1db1009/07/2009D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db10.mdf

    Server1\INST1db1109/09/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db11.mdf

    Server1\INST1db1209/09/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db12.mdf

    Server1\INST1db1309/09/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db13.mdf

    Server1\INST1db1409/09/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db14.mdf

    Server1\INST1db1509/09/2008D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db15.mdf

    Thanks for your help.

  • When you order by the "createddate" it is sorting them in order of the days, rather than the date itself. You could sort by the original date value crdate desc

    Have a look at this example:

    DECLARE @T TABLE

    (

    DT DATETIME

    );

    INSERT INTO @T (DT)

    SELECT '2010-07-01 00:00:00.000' UNION ALL

    SELECT '2010-07-02 00:00:00.000' UNION ALL

    SELECT '2010-06-03 00:00:00.000' UNION ALL

    SELECT '2010-09-04 00:00:00.000' UNION ALL

    SELECT '2010-06-05 00:00:00.000' UNION ALL

    SELECT '2010-08-06 00:00:00.000' UNION ALL

    SELECT '2010-08-07 00:00:00.000' UNION ALL

    SELECT '2010-09-08 00:00:00.000' UNION ALL

    SELECT '2010-06-09 00:00:00.000';

    SELECT DT, CONVERT(VARCHAR(10), DT, 103) AS CreatedDate FROM @T

    ORDER BY DT ASC;

    SELECT DT, CONVERT(VARCHAR(10), DT, 103) AS CreatedDate FROM @T

    ORDER BY DT DESC;

    SELECT DT, CONVERT(VARCHAR(10), DT, 103) AS CreatedDate FROM @T

    ORDER BY CreatedDate ASC;

    SELECT DT, CONVERT(VARCHAR(10), DT, 103) AS CreatedDate FROM @T

    ORDER BY CreatedDate DESC;

    So for your example try:

    SELECT name AS 'Database Name',CONVERT(varchar(10), crdate, 103) AS 'Created Date', filename AS 'MDF Location' FROM dbo.sysdatabases

    ORDER BY crdate DESC

  • Thanks Dohsan. Much appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply