June 3, 2010 at 2:17 am
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.
June 3, 2010 at 3:10 am
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
Cheers Steve. Easy when you know how!
Thanks
June 4, 2010 at 7:19 am
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
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
June 4, 2010 at 8:45 am
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.
June 4, 2010 at 9:29 am
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
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