By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,785 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

Formatting a Date if already using MIN function

P: 1
I have a query where I am using the MIN function to filter out duplicate Call Numbers (Call_No). The query works well and returns 34 records -- perfect. Here it is:

Expand|Select|Wrap|Line Numbers
  1. SELECT RV_CALL_HISTORY.Call_No as "Call Number",
  2. MIN(RV_CALL_HISTORY.Call_Log_Date) as "Call Log Date",
  3. MIN(RV_CALL_HISTORY.Call_Hist_Date) as "Call Hist Date"
  4. FROM RV_CALL_HISTORY
  5. WHERE
  6. RV_CALL_HISTORY.Call_Log_Date > '2012-04-01 00:00'
  7. AND
  8. RV_CALL_HISTORY.Call_Hist_Action_Officer='Joe Blow'
  9. AND
  10. RV_CALL_HISTORY.Call_Log_Date < GETDATE()
  11. GROUP BY RV_CALL_HISTORY.Call_No
---------------------------
Now I would like to convert the Call_Hist_Date to:
YYYY.MM.DD

I have tried using the following, but then I lose the filter. I get 114 records:
Expand|Select|Wrap|Line Numbers
  1. SELECT RV_CALL_HISTORY.Call_No as "Call Number",
  2. MIN(RV_CALL_HISTORY.Call_Log_Date) as "Call Log Date",
  3. CONVERT(VARCHAR(10),RV_CALL_HISTORY.Call_Hist_Date,102) as [YYYY.MM.DD]
  4. FROM RV_CALL_HISTORY
  5. WHERE
  6. RV_CALL_HISTORY.Call_Log_Date > '2012-04-01 00:00'
  7. AND
  8. RV_CALL_HISTORY.Call_Hist_Action_Officer='Joe Blow'
  9. AND
  10. RV_CALL_HISTORY.Call_Log_Date < GETDATE()
  11. GROUP BY RV_CALL_HISTORY.Call_No,RV_CALL_HISTORY.Call_Hist_Date
-------------------------
Any idea how I can combine the MIN and CONVERT functions?
I am using Microsoft SQL Management Studio (Microsoft SQL Server 2008 R2)

Thanks,
skifast
Apr 17 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
Please use code tags when posting code.

Do the conversion around the min.
Apr 18 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.