August 18, 2011 at 10:30 am
I am using SQL Server 2008 and I am reading a table that has a field with dates in it but the field is of type TEXT. I did not create this database and I have no way of changing it. I am trying to get the Min and Max date from this field but I am having no luck.
Let's say the field only has three dates: 7/9/2011, 7/10/2011, 7/2/2011.
When I run a Min(Field) query I get 7/10/2011 as the minimum date and I get 7/9/2011 as the maximum date. What can I do?
Here is the Min query:
SELECT Min(PatientDischargeDate) FROM tblParticipants
Thanks for any help.
August 18, 2011 at 10:57 am
Got this worked out. New query is:
Select Min(CONVERT(DATE,PatientDischargeDate)) AS MinDate,
Max(CONVERT(DATE,PatientDischargeDate)) AS MaxDate
FROM tblParticipants
August 18, 2011 at 11:00 am
this seems to work:
create table test(
datefield text null)
on [primary]
insert test values ('7/11/2011')
insert test values ('7/10/2011')
insert test values ('7/9/2011')
select MIN (cast(cast (datefield as varchar)as date) )from test
August 18, 2011 at 11:03 am
sorry- didnt refresh in time.. glad you have your answer
interesting... when I attempted to use convert from text, like:
create table test(
datefield text null)
on [primary]
insert test values ('7/11/2011')
select Max (convert (date,datefield))from test
I got the error:
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type text to date is not allowed.
hence, the double cast...
August 18, 2011 at 1:29 pm
Let your troubles be a good example and reason why you should store datetime data in a datetime datatype. Datetime information in NOT string data. It is datetime data. You wouldn't store an int and xml field. I don't know why so many people insist on storing datetime as strings. It is slow to convert and frought with errors. Data validation is really difficult when it is strings and it doesn't support more than 1 date format. If at all possible do yourself a favor and change your column(s) datatype(s) to datetime and avoid all this hassle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply