Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Selecting DISTINCT dates

    Hi

    I want to return all the distinct dates from a table. So say if i have 2 entries that have the same date but maybe different time. I can do it the following 2 way. I was wondering if there is a better way to do this

    SELECT DISTINCT DAY(dateSubmitted), MONTH(dateSubmitted),YEAR(dateSubmitted)
    FROM jobUpdate
    WHERE timesheetID IS NULL

    SELECT DISTINCT Convert(varchar(10), dateSubmitted, 103)
    FROM jobUpdate
    WHERE timesheetID IS NULL

    the 2nd way seems to be better, but i heard somewhere that CONVERT shouldn't be use. Is CONVERT goin to be deprecated or something

  2. #2
    Join Date
    Aug 2003
    Posts
    111

    Re: Selecting DISTINCT dates

    All comments welcome

    Cheers
    James

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can do it using a third way:

    selelct * from jobUpdate j
    left outer join (
    SELECT timesheetID, dateSubmitted
    FROM jobUpdate) j1
    on j.timesheetID = j1.timesheetID and j.dateSubmitted = j1.dateSubmitted
    WHERE j1.timesheetID IS NULL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, there has been a lot of heated debate about datetime converions and whole-date truncation on this forum, but your second method is a common method of doing what you want to do. Go with it.

    I would recommend that you use datetime format 120 instead of 103, as 120 format is yyyy-mm-dd and thus sorts correctly, where format 103 does not.

    Also, often the entire expression is wrapped in another CAST (or CONVERT) statement in order to force it back to datetime:

    SELECT DISTINCT CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)

    blindman

    rdjabarov,
    I don't see where your query filters out duplicate date values?
    Last edited by blindman; 09-05-03 at 09:56.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm: me neither, I don't know what I was trying to do...does anybody see what I was trying to demo here?

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I didn't dare to ask .....
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you were trying to demonstrate how long you could get me to look at your code and repeat the phrase "I must be missing something. I must be missing something. I must be missing something."

    You had me going for at least 10 minutes.

    blindman

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's it, you got it!!! Actually, and honestly, I was thinking that if he want distinct, then why does he need to convert? And of course, I completely ignored the fact that he wanted distinct DATES, while having date/time values... DAHHHHHHH Oh well, it was late

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Hey, you like a bunch of good old friends! Have a beer on my costs!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  10. #10
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by rdjabarov
    That's it, you got it!!! Actually, and honestly, I was thinking that if he want distinct, then why does he need to convert? And of course, I completely ignored the fact that he wanted distinct DATES, while having date/time values... DAHHHHHHH Oh well, it was late
    hehehe
    yeah, i just need to find out whether someone entered records into the database on a particular day, i don't care how many entries has been entered, i am just interested in whether an record insertion has occured.

  11. #11
    Join Date
    Aug 2003
    Posts
    111
    Originally posted by blindman
    Well, there has been a lot of heated debate about datetime converions and whole-date truncation on this forum, but your second method is a common method of doing what you want to do. Go with it.

    I would recommend that you use datetime format 120 instead of 103, as 120 format is yyyy-mm-dd and thus sorts correctly, where format 103 does not.

    Also, often the entire expression is wrapped in another CAST (or CONVERT) statement in order to force it back to datetime:

    SELECT DISTINCT CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)

    blindman

    rdjabarov,
    I don't see where your query filters out duplicate date values?
    cheers

Posting Permissions

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