Another date problem
-
Good day
We have a web solution producing management reports using XML for data retrieval. One of the display fields on the report (Effective_Date) must be in the format "dd/mm/yyyy". The Effective_Date field is of type "datetime" which obviously includes the time that I do not want. I know that one of the ways to get around this is by converting the date to a string, but the problem is that the report must be sorted descending on this date field. Below is a sample (not the actual) Select to demonstrate what I am working with. Any help would be greatly appreciated.
'------------------------------
SELECT
[File_ID],
Created_Date,
Effective_Date,
Store_Code,
File_Description,
LTRIM([File_Name]) AS [File_Name],
File_Size,
File_Status
FROM Download_Files
WHERE NOT File_ID IS NULL and store_code = '720'
order by effective_date DESC
-
have a look at "convert" in BOL
you can use:
select convert(char(10), effective_date, 103)
to convert the date to a gb date string
however, if you're going to put it in an xml doc - you should use 126 (I think
- 121 and 126 are the same except 126 has a T in it to separate the date and time - pretty sure that's the one you're meant to use 
).Then you can format the date how you like in your report, output it in the user's locale format etc....
(and also use the date as a date in the xml doc)
-
SELECT convert(char,datew,103)
FROM Table1
order by datew desc
This will give you the desired result.
Andy. -
Thanks! That did the trick.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply