#1
  1. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    12

    Casting UK Date format as date?


    Hi, all.

    I have a table with a column called CreationDate. This is a datetime data type, and by default is in US format. For example:

    2012-03-06 16:32:19.107

    What I want to do is to convert this into UK format, and then use it in the ORDER BY clause so I can display the data sorted by the date.

    When I use the code below (note the 101 conversion type) the casting element works. Presumably because I'm converting to a US date format.

    Code:
    SELECT  CAST(CONVERT(varchar(10), [CreationDate],101) As Date) As ukDate  
      FROM [ApplicationTracking].[dbo].[TimeKeeping]
      ORDER BY [CreationDate] desc
    when I use the code below (note the 103, which converts it to UK date format) it throws the following error:

    'Conversion failed when converting date and/or time from character string.'

    Code:
    SELECT  CAST(CONVERT(varchar(10), [CreationDate],103) As Date) As ukDate  
      FROM [ApplicationTracking].[dbo].[TimeKeeping]
      ORDER BY [CreationDate] desc
    So as a test, I did this:

    Code:
    SELECT 
         ISDATE(CONVERT(varchar(10), [CreationDate],103)) As ukDate  
      FROM [ApplicationTracking].[dbo].[TimeKeeping]
      ORDER BY [CreationDate] desc
    to see if everything returned a date object. What was interesting was that some WERE a date object, and some were NOT. Note that this is a non-nullable field, and every date is in the same format.

    Can anybody advise me on how I can achieve my initial goal?

    Many thanks....
    Captain Planet.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    3
    Rep Power
    0
    try with this code

    Code:
    SELECT cast(datepart(dd, [CreationDate]) as VARCHAR(10)) AS ukDate FROM [ApplicationTracking].[dbo].[TimeKeeping] ORDER BY [CreationDate] desc
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by Captain Planet
    Can anybody advise me on how I can achieve my initial goal?
    it isn't really all too clear what your initial goal is

    you have a DATETIME column and you use CONVERT to turn it into a string, and then you use CAST to convert that string into a DATE

    why not just CAST(CreationDate AS DATE) ??

    dates and datetimes are not stored internally in US or UK or any other format

    if you want to display a certain format, that's what CONVERT will do for you, and i don't understand why you want to convert a display format back into a date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    12
    Yes, I'll try to be more clear.....

    So I have a datetime column - [CreationDate] and an example value is:

    2012-03-06 16:32:19.107

    I want to display this as:

    06/03/2012

    So I use this:

    CONVERT(varchar(10), [CreationDate],103)

    which displays my date correctly. However, I need to sort this column in date order. Since it's now a varchar, it will only sort in alphabetical order. So I try and cast this newly formatted date as a date object, so I can use it in the 'ORDER BY' clause. However, for some reason not all of the dates CAST correctly, because they are not seen as dates? And by using my code examples in post 1, it turns out that converting to UK and then casting is the problem. If I convert to US and cast, it works ok.

    Make sense?


    So the key in all this is to show my date column in dd/MM/yyyy format, and then sort by the same column in date order.
    Last edited by Captain Planet; March 7th, 2012 at 12:13 PM.
    Captain Planet.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    please try this --
    Code:
    SELECT CONVERT(VARCHAR(10),CreationDate,101) AS ukDate  
      FROM ApplicationTracking.dbo.TimeKeeping
    ORDER 
        BY CreationDate DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    12
    Yes - as simple as it looks, it works. I guess it's just confused my because I only thought you could order by a column which is explicitly selected (ie, without 'datepart' functions etc).... Thanks.
    Captain Planet.

IMN logo majestic logo threadwatch logo seochat tools logo