none
Order by datetime (datatype varchar)

    Question

  • Hi!
    First of all. I have searched through the forums regarding this subject, but have not been able to find something that was able to solve my problem.

    I have a table with some columns;  ID, IP, and most important; GETDATE column. This column is of datatype VARCHAR(MAX).
    I'm storing date and time info in it. The format is like this.  2009-07-07 22:00:58.000 > that is year, month, day and then time. 22:00 equals to 10 pm.

    I  would like to sort by this column, but since it is of VARCHAR this gives incorrect result. I have understood that there is something about conversion, but haven't been able to fully understand the examples in different threads.

    So if someone could please help me by writing the full sql string. Something like "SELECT ID, IP, GETDATE FROM MyTable ORDER BY GETDATE DESC"

    One last thing; If I use the CONVERT function in my SELECT statement will it then permanently convert every field within the GETDATE column or just do a "temporary" conversion in order to give a correct result.

    Thanks!

    Tuesday, July 7, 2009 10:02 PM

Answers

  • <<I think that there may be a misunderstanding here. I'm not using the US default; MMDDYYYY, at all.
    I'm getting the date value using C# > DateTime.Now.ToString(), thats it. This gives me a; DD-MM-YYYY HH:MM:SS format. And this is what I'm storing in the SQL GetDate column which is a varchar. So there is no way the US default can creep in somehow.
    >>>

    This is exactly my point.  You are storing the value as DD-MM-YYYY, but your server regional settings dicate how SQL Server interprets a datetime.  If you are in the US, your regional settings dictate MM-DD-YYYY.  Which means that when your string gets converted it try to interpret the date as MM-DD-YYYY, which will cause an overflow.  This is why to use the special conversion style to tell SQL the date is in DD-MM-YYYY format.

    <<There is just one funny thing: If I use the ISDATE to get all the invalid values I get a list that goes from 25-06-2009 - 30-06-2009
    And If I do the opposite I get values that goes from 01-07-2009 - 09-07-2009.
    Maybe this is the thing you all are referring to.
    >>

    This is because of your regional settings, as stated above. 25-06-2009 is invalid because it cannot be convert using your server's regional settings. Month 25, day 6, year 2009, is not a valid date, while month, 1 day 7, year 2009 is. You can use the set option to validate.  set dateformat dmy;.  After doing this the values should all return as a 1, with isdate.

    << Anyway the column GetDate has only 300 rows. I'm thinking of just clearing all rows of this column and then changing its datetype to DateTime (and of couse storing the C# part as DateTime.Now() )
    >>

    This is surly a viable solution.  I just wanted to explain why the failure is occuring.
    http://jahaines.blogspot.com/
    • Marked as answer by SamarDK Thursday, July 9, 2009 1:13 PM
    Thursday, July 9, 2009 12:11 PM
    Moderator

All replies

  • You should not store dates in varchar format especially for presentational purposes.  You can display a datetime value to just about anyway you want with convert.  Keeping datetime values in their native data type eliminates problems such as this and a load of others.

    As to the solution to your problem, just convert the column to a datetime value.

    SELECT ID, IP, GETDATE FROM MyTable ORDER BY CONVERT(DATETIME,GETDATE) DESC

    Edit:I just saw your last line.  The conversion is only for the resultset.  You will not be changing your table definition.

    http://jahaines.blogspot.com/
    Tuesday, July 7, 2009 10:05 PM
    Moderator
  • When you want to sort datetimes stored as strings, you need to convert to datetime to get the desired results:

    Order By convert(datetime,myfield,101)

    Take note of the last parameter - the date format. Depending on your locale, you will want to use a different value.

    JP
    JP Please click the Mark as Answer button if a post solves your problem!
    Tuesday, July 7, 2009 10:06 PM
  • Thanks for the quick reply! and for the tip!
    I  tried your solution however I'm getting an error saying.

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    I can remember from other threads that it is related to some wrong datainput in some cell within the GETDATE column. However this is not the case I have checked all cells within this columns (there are only a few, its a small table) and they all seems okay.

    By the way I tried using this sql string (which gave the above error);
    SELECT ID, GETDATE, IP FROM MyTable  ORDER BY CONVERT(DATETIME, GETDATE) DESC

    NB: Is it so late to change the datatype of the column: GETDATE from varchar(MAX) to datetime.

    Thanks again.
    Tuesday, July 7, 2009 10:26 PM
  • That gets to Adam's point - which is a good one...you don't want to store dates as strings. Suggest you get your stored data into the correct format asap. You may have to tweak the strings you have in order to accomplish that task. It may take a while - but you will be happier in the long run!

    JP


    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, July 7, 2009 10:31 PM
  • I guess it can't be done as simple as just changing the column type from varchar to datetime within sql server management studio.
    Tuesday, July 7, 2009 10:36 PM
  • You can use the ISDATE function to check for invalid date/time values:

    SELECT ID, [GETDATE], IP 
    FROM MyTable 
    WHERE ISDATE([GETDATE]) = 0;

    While you are at changing the table, it is good to rename the column to something more meaningful, GETDATE is a built-in function in SQL Server.
    Plamen Ratchev
    Tuesday, July 7, 2009 11:28 PM
    Moderator
  • That gets to Adam's point - which is a good one...you don't want to store dates as strings. Suggest you get your stored data into the correct format asap. You may have to tweak the strings you have in order to accomplish that task. It may take a while - but you will be happier in the long run!

    JP


    Please click the Mark as Answer button if a post solves your problem!

    You are probably storing an invalid date.  You will need to fix your data before you can proceed.  The other option is to exclude the invalid dates from the resultset, which prevents the overflow. 

    You can use the isdate() function to determine which dates are valid and which are not. If ISDate returns 1 then the date is valid; otherwise, the date is invalid.

    SELECT ID, GETDATE, IP FROM MyTable  WHERE IS_DATE(GETDATE) = 0

    Edit: forgot to mention that once all the dates are valid, you can alter the column to a datetime data type, quite painlessly.

    ALTER TABLE MyTable
    ALTER COLUMN [GETDATE] DATETIME



    http://jahaines.blogspot.com/
    Tuesday, July 7, 2009 11:30 PM
    Moderator
  • I agree with all people who said that you shouldn't store dates as strings like this (certainly using a varchar(max) column is very not good).  But, if that is how you are stuck with, you should still be able to sort your data on the character values if they are actually formatted like yyyy-mm-dd hh:mm:ss.llll it should sort correctly.  This format puts the important stuff first, and unless you have dates earlier than the year 1000, it should work.

    What exactly is wrong with the sorted data?
    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    Wednesday, July 8, 2009 12:20 AM
    Moderator
  • Thanks for the help guys.

    This is were I'm at;

    I would like to correct the invalid values in GetDate column. In order to do this I have used the ISDATE function "WHERE ISDATE(GetDate) = 0" on the GetDate column to check for invalid date/time values, it returns 100 rows. This is how the format of the invalid values looks like (I just took 2 random values).

    25-06-2009 20:41:23
    30-06-2009 22:40:08
    And this is how they all (invalid values) looks like


    I also made the same check only for valid values like this "WHERE ISDATE(GetDate) = 1" . This returned 200 rows. And this is how the format of the valid values looks like;

    01-07-2009 03:31:37
    03-07-2009 13:12:15
    And this is also how they all (valid values) looks like

    Now is it me or do they (invalid and valid values) looks like the same. I mean how or what do I correct in the invalid values?

    P.S. The Datatype for the GetDate column is still varchar(max). I'm not able to ALTER it to DATETIME datatype. It returns an error;  (The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.).
    Its actually the same error I get when I tried to do: SELECT ID, IP, GETDATE FROM MyTable ORDER BY CONVERT(DATETIME,GETDATE) DESC

    Thanks for the help!





    Wednesday, July 8, 2009 2:11 PM
  • You need to convert the format to DDMMYYYY, as the US default is MMDDYYYY.  Essentially, SQL Server thinks you are trying to use a date where the month is 30 and the day is 6, not month 6 day 30. 

    Here is the solution:
    DECLARE @t TABLE(
    id INT,
    dt VARCHAR(30)
    )
    
    INSERT INTO @t VALUES (1,'25-06-2009 20:41:23');
    INSERT INTO @t VALUES (2,'30-06-2009 22:40:08');
    
    SELECT *
    FROM @t
    ORDER BY CONVERT(DATETIME,dt) --<---this fails because the default format for the US is MMDDYYYY
    
    --the solution is to convert the date to the format DDMMYYYY using the 103 format
    
    SELECT *
    FROM @t
    ORDER BY
    	CONVERT(DATETIME,dt,103)

    http://jahaines.blogspot.com/
    Wednesday, July 8, 2009 2:44 PM
    Moderator
  • You can change you column's data type by updating the data and then altering the column.

    Something like this should work.

    --to alter your table do this
    UPDATE t
    SET dt = CONVERT(VARCHAR(30),CONVERT(DATETIME,dt,103),126)
    GO
    
    ALTER TABLE T
    ALTER COLUMN [dt] DATETIME

    http://jahaines.blogspot.com/
    Wednesday, July 8, 2009 2:50 PM
    Moderator
  • You have taken the data from a system with the format MM-DD-YYYY and in your new system the format is DD-MM-YYYY. When the day is smaller than 13 it's regarded as a month, and when it's bigger it's not a valid month because there are not months greater than 12. If I where you I would do
    YEAR

    ORDER BY YEAR+MONTH+DAY
    ORDER BY SUBSTRING(DATE,7,4)+SUBSTRING(DATE,4,2)+SUBSTRING(DATE,1,2)

    And it is goning to order it correctly. I hope it helps

    Wednesday, July 8, 2009 3:26 PM
  • You need to look at the style part of teh convert statement... i.e. 101 in the example below...

    Order By convert(datetime,myfield,101)


    Here is a useful link which describes convert and the different styles... http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

    Regards




    Jon Royales
    Wednesday, July 8, 2009 4:12 PM
  • I think that there may be a misunderstanding here. I'm not using the US default; MMDDYYYY, at all.
    I'm getting the date value using C# > DateTime.Now.ToString(), thats it. This gives me a; DD-MM-YYYY HH:MM:SS format. And this is what I'm storing in the SQL GetDate column which is a varchar. So there is no way the US default can creep in somehow.

    The examples I gave was just randomly taken.
    Just to clear out things, here is a list of values taken from the GetDate column. This list include both valid and unvalid values.
    It should clear out any misunderstanding about the use of US default date format MMDDYYYY.

    09-07-2009 06:38:28
    08-07-2009 10:44:24
    07-07-2009 09:56:21
    06-07-2009 16:29:33
    05-07-2009 12:09:05
    04-07-2009 19:07:58
    03-07-2009 22:28:32
    02-07-2009 18:46:21
    01-07-2009 22:48:14
    30-06-2009 23:07:53
    29-06-2009 21:34:24
    28-06-2009 20:35:05
    27-06-2009 14:21:50
    26-06-2009 13:57:14
    25-06-2009 22:23:44

    This list consist of randomly taken values. I have just taken one value for each day. As it can be seen I started to gather the information the 25 June and the last value has been picked today.

    There is just one funny thing: If I use the ISDATE to get all the invalid values I get a list that goes from 25-06-2009 - 30-06-2009
    And If I do the opposite I get values that goes from 01-07-2009 - 09-07-2009.
    Maybe this is the thing you all are referring to.

    Anyway the column GetDate has only 300 rows. I'm thinking of just clearing all rows of this column and then changing its datetype to DateTime (and of couse storing the C# part as DateTime.Now() )

    Thanks for the continues help
    Thursday, July 9, 2009 10:13 AM
  • Anyway the column GetDate has only 300 rows. I'm thinking of just clearing all rows of this column and then changing its datetype to DateTime (and of couse storing the C# part as DateTime.Now() )



    This is your best option... and it would be better to do it now while you only have a few rows in the table.

    Regards


    Jon Royales
    Thursday, July 9, 2009 10:22 AM
  • <<I think that there may be a misunderstanding here. I'm not using the US default; MMDDYYYY, at all.
    I'm getting the date value using C# > DateTime.Now.ToString(), thats it. This gives me a; DD-MM-YYYY HH:MM:SS format. And this is what I'm storing in the SQL GetDate column which is a varchar. So there is no way the US default can creep in somehow.
    >>>

    This is exactly my point.  You are storing the value as DD-MM-YYYY, but your server regional settings dicate how SQL Server interprets a datetime.  If you are in the US, your regional settings dictate MM-DD-YYYY.  Which means that when your string gets converted it try to interpret the date as MM-DD-YYYY, which will cause an overflow.  This is why to use the special conversion style to tell SQL the date is in DD-MM-YYYY format.

    <<There is just one funny thing: If I use the ISDATE to get all the invalid values I get a list that goes from 25-06-2009 - 30-06-2009
    And If I do the opposite I get values that goes from 01-07-2009 - 09-07-2009.
    Maybe this is the thing you all are referring to.
    >>

    This is because of your regional settings, as stated above. 25-06-2009 is invalid because it cannot be convert using your server's regional settings. Month 25, day 6, year 2009, is not a valid date, while month, 1 day 7, year 2009 is. You can use the set option to validate.  set dateformat dmy;.  After doing this the values should all return as a 1, with isdate.

    << Anyway the column GetDate has only 300 rows. I'm thinking of just clearing all rows of this column and then changing its datetype to DateTime (and of couse storing the C# part as DateTime.Now() )
    >>

    This is surly a viable solution.  I just wanted to explain why the failure is occuring.
    http://jahaines.blogspot.com/
    • Marked as answer by SamarDK Thursday, July 9, 2009 1:13 PM
    Thursday, July 9, 2009 12:11 PM
    Moderator
  • Ok!
    Thanks you everyone.
    I will go for the clearing of the column!

    Thursday, July 9, 2009 1:14 PM