Dates stored in SQL Do Not Match Appointments

3 posts, 0 answers
  1. Chris
    Chris avatar
    3 posts
    Member since:
    Sep 2013

    Posted 14 Apr 2014 Link to this post

    I have been trying to solve this issue for three days now and it is driving me nuts.
    I am sure it is just a setting that I have not set or something I have forgot to do so here I am looking for any help I can get.

    Visual Studio 2010 ultimate
    SQL 2008 Enterprise
    .net 4.0
    Telerik.Web.UI, v2013.1.403.40

    OK here is the scenario.
    Add an appointment to RadScheduler and it works fine, you can carry out all the functions and RadScheduler handles the data fine. It shows appointments in the correct time slots and right dates.
    If you look in the SQL Database at the data the Start and End dates and times do not match what RadScheduler shows.
    If you enter an appointment before 9 am it shows with the date of the previous day. After 9 am has correct date but wrong time. None of the appoints have the correct times.
    I have checked my Regional settings, set the correct TimeZoneID in RadScheduler and am now at my wits end.
    The only reason I am worrying about it is that i need to access the data in the SQL database from another function and I cant gather the correct appointments by date or times as none of them match what's in the SQL Database.

    I have attached some screen shots that better show the process and matching data. I have only a single appointment to help keep it simple.

    I put a debug message in the RadScheduler Insert and Update appointment and this is the data I captured

    This was for the 9 am appointment image (SQL1)
    Update Start Time= 14/04/2014 11:30:00 PM
    Update End Time 15/04/2014
    Update Time Zone= E. Australia Standard Time
    Update Duration= 00:30:00

    This was for the 9:30 am appointment image (SQL2)
    Update Start Time= 14/04/2014 11:00:00 PM
    Update End Time 14/04/2014 11:30:00 PM
    Update Time Zone= E. Australia Standard Time
    Update Duration= 00:30:00

    This is the 10 am appointment (SQL3)
    Update Start Time= 14/04/2014 11:30:00 PM
    Update End Time 15/04/2014
    Update Time Zone= E. Australia Standard Time
    Update Duration= 00:30:00

    <telerik:RadScheduler ID="QuoteScheduler" runat="server" 
                  DataDescriptionField="Description" DataEndField="End" DataKeyField="ID" 
                  DataReminderField="Reminder" DataStartField="Start" 
                  DataSubjectField="Subject" Height="587px" Skin="Office2010Black" 
                  TimeZoneID="E. Australia Standard Time" TimeZoneOffset="10:00:00" 
                  DataSourceID="QuoteAppointSQL" RowHeight="22px" 
                  StartInsertingInAdvancedForm="True" EnableTheming="True" 
                  Font-Names="Calibri" Font-Size="Small" EditFormTimeFormat="hh:mm" HoursPanelTimeFormat="htt"
                  FirstDayOfWeek="Monday" LastDayOfWeek="Sunday" DayStartTime="04:30:00" DayEndTime="19:00:00" 
                  WorkDayStartTime="04:30:00"  WorkDayEndTime="19:00:00" EnableCustomAttributeEditing="True">
                  <ExportSettings FileName="Quote Appointments.pdf">
                    <Pdf PageHeight="297mm" PageWidth="210mm" PaperSize="A4" Producer="mrConcrete" />
                  <AdvancedForm Modal="True" TimeFormat="hh:mm" EnableCustomAttributeEditing="True" />
                    <telerik:ResourceType DataSourceID="ResourceSQL" ForeignKeyField="UserID" KeyField="ID" Name="AdminUser" TextField= "FullName" />
                    <Localization AdvancedSubject="Appointment Subject" Save="Save Appointment" />
                    <TimelineView ColumnHeaderDateFormat="ddd-MM" HeaderDateFormat="ddd-MM" />
                    <DayView DayStartTime="04:00:00" WorkDayEndTime="19:00:00" WorkDayStartTime="04:00:00" />
                    <MonthView HeaderDateFormat="MMMM, yyyy" />

    Code Behind in FormCreate

    If e.Container.Mode <> SchedulerFormMode.Insert AndAlso e.Container.Mode <> SchedulerFormMode.Edit Then
          Dim Combo As RadComboBox = TryCast(e.Container.FindControl("ResAdminUser"), RadComboBox)
          FindName = GetData("Select FirstName from Users where ID=" & Me.UserNum.Text)
          FindName = FindName & " " & GetData("Select Surname from Users where ID=" & Me.UserNum.Text)
          If Combo.Text <= "" Then Combo.SelectedIndex = Combo.FindItemIndexByText(FindName)

          Dim StartDate As RadDatePicker = TryCast(e.Container.FindControl("StartDate"), RadDatePicker)
          StartDate.SelectedDate = Today

          Dim StartTime As RadTimePicker = TryCast(e.Container.FindControl("StartTime"), RadTimePicker)

          StartTime.TimeView.Interval = New TimeSpan(0, 30, 0)
          StartTime.TimeView.TimeFormat = "hh:mm tt"
          StartTime.TimeView.StartTime = TimeSpan.FromHours("4.00") 
          StartTime.TimeView.EndTime = TimeSpan.FromHours("18.00")
          StartTime.TimeView.Columns = 4

          StartTime.TimeView.HeaderText = "Start Time For " & FindName
          startTime.TimeView.DataList.DataSource = Nothing

          Dim EndTime As RadTimePicker = TryCast(e.Container.FindControl("EndTime"), RadTimePicker)
          EndTime.TimeView.Interval = New TimeSpan(0, 30, 0)
          EndTime.TimeView.TimeFormat = "hh:mm tt"
          EndTime.TimeView.StartTime = TimeSpan.FromHours("04.00") 
          EndTime.TimeView.EndTime = TimeSpan.FromHours("19.00") 
          EndTime.TimeView.Columns = 4

          EndTime.TimeView.HeaderText = "End Time For " & FindName
          EndTime.TimeView.DataList.DataSource = Nothing
        End If
    end if

    I am not sure what else I can supply but I hope this enough for somebody much smarter then me to point me in the right direction of give me some hints and what I am doing wrong.

    Thanks in advance

  2. Plamen
    Plamen avatar
    3079 posts

    Posted 17 Apr 2014 Link to this post

    Hello Chris,

    The issue you observe is caused by the fact that you use TimeZoneID and TimeZoneOffset properties. In this case the appointments are stored in the database in UTC time and are displayed in the timezone you have set. This behavior is intended in cases when you want to be able to set different timezones as in this on-line demo.

    If you want to have the appointments displayed in the same time as they are declared in the datasource you have to remove both TimeZoneID and TimeZoneOffset properties.

    Hope this will explain the issue.


    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

  3. Chris
    Chris avatar
    3 posts
    Member since:
    Sep 2013

    Posted 28 Apr 2014 in reply to Plamen Link to this post

    Thanks Plamen,
    I should have said thanks to you earlier but was so happy to get your answer I just ploughed on with work once you pointed out what I was doing wrong.
    You were spot on and thanks, saved me from going completely mad ha ha

Back to Top