Logs/SavedLogs/CompareDateExtractionQueries.txt 


/// THIS SNIPPET WORKS on BOTH Dev and Live /////////////////////////////////

but it gives me 24 hour time "Mon 19 May - 17:30"

StringBuilder sbDateTimeQuery = new StringBuilder();

 //DateTimeQuery.Append("select "); 
 sbDateTimeQuery.AppendLine(",case extract( weekday from T2.EventStart) ");
 sbDateTimeQuery.AppendLine("when 0 then 'Sun' ");
 sbDateTimeQuery.AppendLine("when 1 then 'Mon' ");
 sbDateTimeQuery.AppendLine("when 2 then 'Tue' ");
 sbDateTimeQuery.AppendLine("when 3 then 'Wed' ");
 sbDateTimeQuery.AppendLine("when 4 then 'Thu' ");
 sbDateTimeQuery.AppendLine("when 5 then 'Fri' ");
 sbDateTimeQuery.AppendLine("when 6 then 'Sat' ");
 sbDateTimeQuery.AppendLine("end  ");

 sbDateTimeQuery.AppendLine("|| ' ' ||   ");
 sbDateTimeQuery.AppendLine("extract(day from T2.EventStart)  ");

 sbDateTimeQuery.AppendLine("|| ' ' || ");
 sbDateTimeQuery.AppendLine("case extract(month from T2.EventStart)  ");
 sbDateTimeQuery.AppendLine("when 1 then 'Jan'  ");
 sbDateTimeQuery.AppendLine("when 2 then 'Feb'  ");
 sbDateTimeQuery.AppendLine("when 3 then 'Mar'  ");
 sbDateTimeQuery.AppendLine("when 4 then 'Apr'  ");
 sbDateTimeQuery.AppendLine("when 5 then 'May'  ");
 sbDateTimeQuery.AppendLine("when 6 then 'Jun'  ");
 sbDateTimeQuery.AppendLine("when 7 then 'Jul'  ");
 sbDateTimeQuery.AppendLine("when 8 then 'Aug'  ");
 sbDateTimeQuery.AppendLine("when 9 then 'Sep'  ");
 sbDateTimeQuery.AppendLine("when 10 then 'Oct' ");
 sbDateTimeQuery.AppendLine("when 11 then 'Nov' ");
 sbDateTimeQuery.AppendLine("when 12 then 'Dec' ");
 sbDateTimeQuery.AppendLine("end  ");

 sbDateTimeQuery.AppendLine("|| ' - ' ||  ");
 sbDateTimeQuery.AppendLine("extract(Hour from T2.EventStart)  ");
 sbDateTimeQuery.AppendLine("|| ':'|| ");
 sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart)  ");
 //DateTimeQuery.AppendLine("--|| ', '|| ");
 //DateTimeQuery.AppendLine("--extract(year from T2.EventStart)  ");
 sbDateTimeQuery.AppendLine("as TheEvDate  ");

 sbDateTimeQuery.AppendLine(",extract(hour from T2.EventStart) ");
 sbDateTimeQuery.AppendLine("|| ':'|| ");
 sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart)  ");
 sbDateTimeQuery.AppendLine("as TheTime ");



//// THIS SNIPPET WORKS ON DEV, but FAILS on live. (even tho it Works on live FlameRobin) //////////////

it gives me 12 hour time "Mon 19 May - 5:30"

      StringBuilder sbDateTimeQuery = new StringBuilder();


      sbDateTimeQuery.AppendLine(" ( case extract(month from T2.EventStart)  ");
      sbDateTimeQuery.AppendLine("when 1 then 'Jan'  ");
      sbDateTimeQuery.AppendLine("when 2 then 'Feb'  ");
      sbDateTimeQuery.AppendLine("when 3 then 'Mar'  ");
      sbDateTimeQuery.AppendLine("when 4 then 'Apr'  ");
      sbDateTimeQuery.AppendLine("when 5 then 'May'  ");
      sbDateTimeQuery.AppendLine("when 6 then 'Jun'  ");
      sbDateTimeQuery.AppendLine("when 7 then 'Jul'  ");
      sbDateTimeQuery.AppendLine("when 8 then 'Aug'  ");
      sbDateTimeQuery.AppendLine("when 9 then 'Sep'  ");
      sbDateTimeQuery.AppendLine("when 10 then 'Oct' ");
      sbDateTimeQuery.AppendLine("when 11 then 'Nov' ");
      sbDateTimeQuery.AppendLine("when 12 then 'Dec' ");
      sbDateTimeQuery.AppendLine("end  ");


      sbDateTimeQuery.AppendLine("|| ' ' ||   ");
      sbDateTimeQuery.AppendLine("extract(day from T2.EventStart)  ");


      sbDateTimeQuery.AppendLine("|| ' ' || ");
      sbDateTimeQuery.AppendLine("case extract(weekday from T2.EventStart) ");
      sbDateTimeQuery.AppendLine("when 0 then 'Sun' ");
      sbDateTimeQuery.AppendLine("when 1 then 'Mon' ");
      sbDateTimeQuery.AppendLine("when 2 then 'Tue' ");
      sbDateTimeQuery.AppendLine("when 3 then 'Wed' ");
      sbDateTimeQuery.AppendLine("when 4 then 'Thu' ");
      sbDateTimeQuery.AppendLine("when 5 then 'Fri' ");
      sbDateTimeQuery.AppendLine("when 6 then 'Sat' ");
      sbDateTimeQuery.AppendLine("end  ");


      sbDateTimeQuery.AppendLine("|| ' - ' ||  ");
      sbDateTimeQuery.AppendLine(" case ");
      sbDateTimeQuery.AppendLine("when extract(Hour from T2.EventStart)  = 0 then 12 ");
      sbDateTimeQuery.AppendLine("when extract(Hour from T2.EventStart)  > 12 then extract(Hour from T2.EventStart) -12 ");
      sbDateTimeQuery.AppendLine("else extract(Hour from T2.EventStart) ");
      sbDateTimeQuery.AppendLine(" end ");


      sbDateTimeQuery.AppendLine("|| ':'|| ");
      sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart)  ");        

//want to add am or pm

      sbDateTimeQuery.AppendLine(" || ' - ' || "); 
      sbDateTimeQuery.AppendLine(" T2.EventName ");


      sbDateTimeQuery.AppendLine(" ) as NameDate  ");                                //as TheDate





//////// This is the from and where used for both versions /////////////

    sbQuery.AppendLine(" from CalEvVenue T1 ");
    sbQuery.AppendLine(" join CalEvEvent T2 on T1.VenId=T2.VenId ");
    sbQuery.AppendLine(" left outer join CALEVEVENT2ARTIST T3 on T2.CalEvId=T3.CalEvId ");
    sbQuery.AppendLine(" left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId ");

    sbQuery.AppendLine(" where T2.EventStart > ");
    sbQuery.Append("'");
    sbQuery.Append(dtThreeDaysAgo.ToString("MM/dd/yyyy HH:mm"));
    sbQuery.Append("'");
    sbQuery.Append(" and T2.ShouldDisplay = 1 ");
    sbQuery.AppendLine(" order by T2.EVENTSTART; ");



//end doc