0

I'm querying a max datetime to bring in the most recent record from another table surrounding a common ID coloumn. I would like to however format this datetime in the standard UK dd/mm/yyyy format. How would I do so? I just cant seem to get this...

Here is my code:

    (SELECT TOP 1 MemberPayments.CoverFinishDay
FROM
    Members LEFT JOIN MemberPayments
        ON Members.MemberID = MemberPayments.MemberID
        AND CoverFinishDay = (
            SELECT MAX(CoverFinishDay)
            FROM MemberPayments
            WHERE Members.MemberID = MemberPayments.MemberID
        ))

CoverFinishDay is stored in standard american datetime. currently the query works, just in the wrong format. I need the output in dd/mm/yyyy

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chen_Dogg
  • 91
  • 1
  • 12
  • 2
    Why do you need it in text format in the database? Isn't that something for your presentation layer? – adrianm Jul 02 '14 at 11:23
  • Which DBMS you are using? – Ankit Bajpai Jul 02 '14 at 11:34
  • possible duplicate of [How to convert DateTime to VarChar](http://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar) – Kevin Hogg Jul 02 '14 at 11:42
  • this is a direct database query that will be presented in a presentation layer. Difficult to explain, in-house Rapid development enviroment. SO the resultant value will exist in a coloumn alongside other values (member name, ID no etc...) – Chen_Dogg Jul 02 '14 at 17:32

2 Answers2

0

If you are using SQL Server 2012 you can convert any date using FORMAT function. In your case you can do like this: 

SELECT FORMAT (MemberPayments.CoverFinishDay, 'd', 'en-GB')

For mor info you can access the page: http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bogdan
  • 1
  • 1
  • 1
0

This will help you: 

SELECT CONVERT(VARCHAR, GETDATE(), 105)

This is for SQL Sever

Saurabh R S
  • 3,037
  • 1
  • 34
  • 44