Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    10

    Unanswered: date sorting in varchar

    hello all.

    i am pulling data from a read only database and the dates are i think in varchar format (they sort by 01-01-2012, 01-02-2011...etc)

    the dates are also in US format.
    how can i convert these to a EUROPE date format and also datetime so that i can use the > and just show logged events greater than a specific date
    Code:
     
    SELECT     logId, logdate, logdesc
    FROM       dbo.log
    WHERE     (logdate >'07/07/2012')
    the code about will not work as the logdate is in varchar.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    SELECT     logId, logdate, logdesc
    FROM       dbo.log
    WHERE     (CONVERT(DATE, logdate, 101) > '2012/07/07') -- yyyy/mm/dd
    Hope this helps.

  3. #3
    Join Date
    Feb 2011
    Posts
    10
    super! its putting me in the right direction.. i had to change the code to this but it works..

    Code:
    SELECT     logId, logdate, logdesc
    FROM       dbo.log
    WHERE     (CONVERT(DATEtime, logdate, 101) > '2012/07/07') -- yyyy/mm/dd
    ...but it still outputs the date in US format...
    anyway to change this to EU format?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, either set your locale (on the client or app server as appropriate) or use SET DATEFORMAT as a last resort.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2011
    Posts
    10
    i havent got access to set format on the app server as i have just got read privileges to the database. it is dodgy using the set dateformat in the sql code? how would i use it? thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    We need to step back from the immediate problem to look at the larger picture before I can answer your question in a way that I'm comfortable is correct.

    The Windows/Web concept of locale is powerful and it is an important tool for providing the appropriate experience for the end user. If there is any way to manage the factors governed by locale by using or respecting the end user's locale setting, that is by far my first choice... It means that the user gets to decide how they want to see and do things, and it is portable across many platforms.

    The first problem comes from the VARCHAR datatype of the [dbo].[log].[logdate] column. There are only a very, very few reasons why I would allow dates to be stored as characters in anything but an ETL staging table. Are you sure that all of the rows in the log table have valid dates and that those dates are formatted the same way? You can check this using:
    Code:
    SET DATEFORMAT YMD;
    
    SELECT *
       FROM dbo.log AS a
       WHERE 0 = IsDate(logdate)
    If this query returns any rows, you'll have to fix them first before you can proceed with confidence. If you have a choice, I'd convert the column from VARCHAR to a DATETIME datatype as this point.

    It may be a bit more work at this point to "do it right" and use locale settings, but it will never be any easier than it is now and in most cases it only becomes harder as time goes on and the application/system evolves. If you have the choice, do it right instead of having to do it over!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2011
    Posts
    10
    got it! thanks for all the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •