March 7th, 2012, 05:33 AM
-
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.
March 7th, 2012, 09:37 AM
-
try with this code
Code:
SELECT cast(datepart(dd, [CreationDate]) as VARCHAR(10)) AS ukDate
FROM [ApplicationTracking].[dbo].[TimeKeeping]
ORDER BY [CreationDate] desc
March 7th, 2012, 09:54 AM
-
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
March 7th, 2012, 11:10 AM
-
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 11:13 AM.
Captain Planet.
March 7th, 2012, 11:33 AM
-
please try this --
Code:
SELECT CONVERT(VARCHAR(10),CreationDate,101) AS ukDate
FROM ApplicationTracking.dbo.TimeKeeping
ORDER
BY CreationDate DESC
March 8th, 2012, 02:37 AM
-
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.