Date Conversion

Question

Date Conversion

Shabba

SSCertifiable

Points: 5007
More actions
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
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.

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

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

Shabba SSCertifiable Points: 5007 More actions · Answer 2

Shabba

SSCertifiable

Points: 5007

June 3, 2010 at 3:21 am

#1175777

Cheers Steve. Easy when you know how!

Thanks

Shabba SSCertifiable Points: 5007 More actions · Answer 3

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?

WayneS SSC Guru Points: 95460 More actions · Answer 4

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

Shabba SSCertifiable Points: 5007 More actions · Answer 5

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.

Dohsan SSCrazy Points: 2972 More actions · Answer 6

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

Shabba SSCertifiable Points: 5007 More actions · Answer 7

Shabba

SSCertifiable

Points: 5007

June 7, 2010 at 12:54 am

#1176948

Thanks Dohsan. Much appreciated.