You are viewing limited content. For full access, please sign in.

Question

Question

How to set date format in SQL lookup

asked on January 2, 2019 Show version history

Hi!

What I'm trying to do is find a way to standardize the date formatting from an SQL lookup. The reason why I need this is that dates are stored in SQL in yyyy-MM-dd format, but are being displayed in Forms as dd/MM/yyyy. I'm trying to do a simple lookup in Forms by populating the dropdown with all the dates in SQL, then bringing up the row where the date matches from that same table.

I've changed the default system clock on both the machine running Forms Server, as well as the client machine, but that didn't help. I've also changed the RoutingService config file, set ignore language to true, and specified the date format default I'd like, and that also didn't work.

It's worth noting that this setup worked last week, but the dropdown was being populated with a datestamp instead of a converted short date. Not certain why that happened, to be honest.

I've added two screenshots; one of the drop down population lookup, and one of the table populating lookup. The dropdown population works, but the table one does not.

Thanks!

Peter

Populate DropDown.PNG
Populate Data.PNG
0 0

Answer

SELECTED ANSWER
replied on January 3, 2019

Hey Chris,

I have tried something along those lines, but in practice, I couldn't get the Date field to populate with the selected date properly.

I ended up doing something a little different, but similar to your idea. Instead of populating a dropdown, I used a date picker, and some javascript to only allow the selection of a proper date. I formatted the date picker to have the proper format, and that worked like a charm. Here's the snippet of javascript I ended up using (use the CSS class myDate on the datepicker):

$(document).ready(function(){
  function isCorrectDate(n) {
    var s = new Date("2017","03","08"); //Seed date, can be any appropriate date
    return (Math.ceil((s - n) / 86400000)%14==0); //Returns true on every day that is a two week increment from seed date
  }
  function checkDate(n) {
    return[isCorrectDate(n),""];
  }
  function checkField(input, inst) {
    if ($(input).closest('li').hasClass('myDate')) {
      $(input).datepicker("option", {beforeShowDay: checkDate});
    }
  }
  $.datepicker.setDefaults( {beforeShow: checkField} );
  window.Parsley.addValidator('secondsat', {
    validateString: function(value) {
      return isCorrectDate(new Date(value); //Validation condition, set to true to disable validation 
    },
    messages: {
      en: 'Not valid date.'
    }
  });
  $('.myDate input').attr('data-parsley-secondsat','');
})

 

2 0

Replies

replied on January 3, 2019 Show version history

Can you format the date to a 'short date' in a separate field and then use the new field for the lookup?

Maybe use the date/time variable as the default value variable? If not, use javaScript to reformat it into the new field.

Just a few thoughts on this. Hope they help.

Chris

0 0
SELECTED ANSWER
replied on January 3, 2019

Hey Chris,

I have tried something along those lines, but in practice, I couldn't get the Date field to populate with the selected date properly.

I ended up doing something a little different, but similar to your idea. Instead of populating a dropdown, I used a date picker, and some javascript to only allow the selection of a proper date. I formatted the date picker to have the proper format, and that worked like a charm. Here's the snippet of javascript I ended up using (use the CSS class myDate on the datepicker):

$(document).ready(function(){
  function isCorrectDate(n) {
    var s = new Date("2017","03","08"); //Seed date, can be any appropriate date
    return (Math.ceil((s - n) / 86400000)%14==0); //Returns true on every day that is a two week increment from seed date
  }
  function checkDate(n) {
    return[isCorrectDate(n),""];
  }
  function checkField(input, inst) {
    if ($(input).closest('li').hasClass('myDate')) {
      $(input).datepicker("option", {beforeShowDay: checkDate});
    }
  }
  $.datepicker.setDefaults( {beforeShow: checkField} );
  window.Parsley.addValidator('secondsat', {
    validateString: function(value) {
      return isCorrectDate(new Date(value); //Validation condition, set to true to disable validation 
    },
    messages: {
      en: 'Not valid date.'
    }
  });
  $('.myDate input').attr('data-parsley-secondsat','');
})

 

2 0
replied on January 3, 2019 Show version history

Nice, glad you got it going! This will likely help someone else out down the road.

1 0
replied on July 15, 2022

Worked for me, too!  Exactly the same problem and your solution worked, Peter!  Thanks!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.