Post reply

Date Conversion

  • June 3, 2010 at 2:17 am

    #223366

    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

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

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

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

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

    What's the best way of achieving this?

    Many Thanks.

  • June 3, 2010 at 3:10 am

    #1175770

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

  • June 3, 2010 at 3:21 am

    #1175777

    Cheers Steve. Easy when you know how!

    Thanks

  • June 4, 2010 at 7:19 am

    #1176507

    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?

  • June 4, 2010 at 7:31 am

    #1176512

    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

  • June 4, 2010 at 8:45 am

    #1176569

    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\INST1 db1 01/05/2009 D:\MSSQLSERVER\MSSQL.5\MSSQL\DATA\db1.mdf

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Thanks for your help.

  • June 4, 2010 at 9:29 am

    #1176593

    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

  • June 7, 2010 at 12:54 am

    #1176948

    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