[Captain Planet]

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.

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.'

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:

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....

[love4all1080]

try with this code

 SELECT cast(datepart(dd, [CreationDate]) as VARCHAR(10)) AS ukDate
 FROM [ApplicationTracking].[dbo].[TimeKeeping]
 ORDER BY [CreationDate] desc

[r937]

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

[Captain Planet]

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.

[r937]

please try this --

SELECT CONVERT(VARCHAR(10),CreationDate,101) AS ukDate  
  FROM ApplicationTracking.dbo.TimeKeeping
ORDER 
    BY CreationDate DESC

[Captain Planet]

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.