Change Date Picker Automatically

JavaScript
21

Well you can count me out of the big bucks conversation, but have got the info you need I hope -

CREATE TABLE [dbo].[Intranet](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Contract_Number] [nvarchar](8) NULL,
[Contract_Status] [nvarchar](100) NOT NULL,
[Company_Name] [nvarchar](250) NULL,
[Invoice_Street] [nvarchar](100) NULL,
[Invoice_City] [nvarchar](100) NULL,
[Invoice_State] [nvarchar](100) NULL,
[Invoice_Country] [nvarchar](100) NULL,
[Invoice_Postcode] [nvarchar](30) NULL,
[Invoice_Tax_Code] [nvarchar](50) NULL,
[Invoice_Telephone] [nvarchar](50) NULL,
[Invoice_Fax] [nvarchar](50) NULL,
[Invoice_Contact_Name] [nvarchar](100) NULL,
[Invoice_Email] [nvarchar](100) NULL,
[Annual_Contract_Value] [numeric](18, 2) NULL,
[Invoicing_Profile] [nvarchar](50) NULL,
[Invoice_Paid_By] [nvarchar](100) NULL,
[Currency_of_Contract] [nvarchar](50) NOT NULL,
[Contract_Start] [date] NULL,
[Contract_End] [date] NULL,
[Renewal_Date] [date] NULL,
[Contract_Length] [int] NULL,
[Quote_Number] [nvarchar](20) NULL,
[Site_Name] [nvarchar](200) NOT NULL,
[Street] [nvarchar](100) NULL,
[City] [nvarchar](100) NULL,
[State] [nvarchar](100) NOT NULL,
[Country] [nvarchar](50) NULL,
[Postcode] [nchar](30) NULL,
[Type_of_Establishment] [nvarchar](100) NOT NULL,
[No_of_Rooms] [int] NULL,
[Group_Name] [nvarchar](80) NOT NULL,
[Invoice_Value] [int] NULL,
[Invoice_Currency] [nvarchar](50) NULL,
[Payment_Contact] [nvarchar](50) NULL,
[Payment_Contact_Tel] [nvarchar](50) NULL,
[Invoice_Frequency] [nvarchar](100) NULL,
[Seasonal_or_Full] [int] NULL,
[Month_Opens] [nvarchar](50) NULL,
[Month_Closes] [nvarchar](50) NOT NULL,
[Brandcheck_Frequency] [int] NULL,
[StandardsCheck_Frequency] [int] NULL,
[Roomcheck_Frequency] [int] NULL,
[Foodcheck_Frequency] [int] NULL,
[Crisischeck_Frequency] [int] NULL,
[Poolcheck_Frequency] [int] NULL,
[Firecheck_Frequency] [int] NULL,
[Aquacheck_Frequency] [int] NULL,
[Spacheck_Frequency] [int] NULL,
[Safetycheck_Frequency] [int] NULL,
[Accesscheck_Frequency] [int] NULL,
[Ecocheck_Frequency] [int] NULL,
[Supplycheck_Frequency] [int] NULL,
[Dinecheck_Frequency] [int] NULL,
[Tourcheck_Frequency] [int] NULL,
[Training_Days] [nvarchar](10) NULL,
[Labcheck_Aqua_Frequency] [int] NULL,
[Labcheck_Food_Frequency] [int] NULL,
[Labcheck_Pool_Frequency] [int] NULL,
[Labcheck_Room_Frequency] [int] NULL,
[Legionella_Test_Frequency] [int] NULL,
[Additional_Sampling] [int] NULL,
[No_of_Modules] [int] NOT NULL,
[Principle_Contact] [nvarchar](100) NULL,
[Principle_Telephone] [nvarchar](50) NULL,
[Preferred_Language] [nvarchar](50) NULL,
[Principle_Contact_Job_Title] [nvarchar](100) NULL,
[Principle_Contact_Email] [nvarchar](100) NULL,
[contract_File] [nvarchar](500) NULL,
[date_Created] [date] NOT NULL,
[date_Modified] [date] NOT NULL,
[Date_Disabled] [date] NULL,
[Changelog] [nvarchar](max) NULL,
 CONSTRAINT [PK_Intranet] PRIMARY KEY CLUSTERED
22

After a bit of google-fu I see the following useful information from an article on How to Set the Day/Month/Year Date Format in SQL Server

[quote]If the date format is not taken into consideration by application developers, a rare situation may arise where an application is inserting dates into tables or using dates in WHERE clauses that are invalid. For example, a given date like 20/05/97 will only be processed if the date format is DD/MM/YY. However, a date like 12/05/97 will be processed with both the DD/MM/YY and MM/DD/YY formats, possibly resulting in the wrong date being used.

A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is “YYYYMMDD” (no separators).[/quote]

So, I suggest that you try using the “YYYYMMDD” string format when sending dates to the database.

23

I did look on google, but wasn’t finding what I needed as didn’t know where they problem was I think, but thanks for getting back to me.

So do you mean as below -

$cStart1b=date('YYYYMMDD', strtotime($_POST['contractStart1']));
$cEnd1b=date('YYYYMMDD', strtotime($_POST['contractEnd1']));
$rDate1b=date('YYYYMMDD', strtotime($_POST['renewalDate1']));

Or within the jscript

<script>
$("#endDate").datepicker({
dateFormat: "YYYYMMDD",
onSelect: function(dateText, instance) {
    date = $.datepicker.parseDate(instance.settings.dateFormat, dateText, instance.settings);
    date.setMonth(date.getMonth() + 11);
    $("#renewalDate").datepicker("setDate", date);
}
});
$("#renewalDate").datepicker({  
dateFormat: "YYYYMMDD"
});
$("#startDate").datepicker({  
dateFormat: "YYYYMMDD"
});
</script>
24

Tried the jscript version and its def not that and tried below and again I had the same flipping error, ahhhh!

$cStart1b=date('YYYYMMDD', strtotime($_POST['contractStart1']));
$cEnd1b=date('YYYYMMDD', strtotime($_POST['contractEnd1']));
$rDate1b=date('YYYYMMDD', strtotime($_POST['renewalDate1']));

Error in executing query.
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 8114 [code] => 8114 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to numeric. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to numeric. ) )

25

Ignoring your existing code for a while, can you create any kind of SQL statement that successfully adds a row to the database?

Starting from that simple beginning and working forward from there seems to be the way to go.

26

No your right Paul, there something wrong and I’m not sure why.

I basically trimmed the insert statement down to the first two fields so by passing any datepickers, and arrays I have and still its not working. So have var_dumped the lot out and this is what I got, but still the same error.

string(1462) "INSERT INTO Intranet (Contract_Number,Contract_Status,Company_Name,Invoice_Street,Invoice_City,Invoice_State,Invoice_Country,Invoice_Postcode,Invoice_Tax_Code,Invoice_Telephone,Invoice_Fax,Invoice_Contact_Name,Invoice_Email,Annual_Contract_Value,Invoicing_Profile,Invoice_Paid_By,Currency_of_Contract,Contract_Start,Contract_End,Renewal_Date,Contract_Length,Quote_Number,Site_Name,Street,City,Country,State,Postcode,Type_of_Establishment,No_of_Rooms,Group_Name,Invoice_Value,Invoice_Frequency,Payment_Contact,Payment_Contact_Tel,Invoice_Currency,Seasonal_or_Full,Month_Opens,Month_Closes,Brandcheck_Frequency,StandardsCheck_Frequency,Roomcheck_Frequency,Foodcheck_Frequency,Crisischeck_Frequency,Poolcheck_Frequency,Firecheck_Frequency,Aquacheck_Frequency,Spacheck_Frequency,Safetycheck_Frequency,Accesscheck_Frequency,Ecocheck_Frequency,Supplycheck_Frequency,Dinecheck_Frequency,Tourcheck_Frequency,Training_Days,Labcheck_Aqua_Frequency,Labcheck_Food_Frequency,Labcheck_Pool_Frequency,Labcheck_Room_Frequency,Legionella_Test_Frequency,No_of_Modules,Additional_Sampling,Principle_Contact,Principle_Telephone,Preferred_Language,Principle_Contact_Job_Title,Principle_Contact_Email,contract_File,date_Created)VALUES('DOM12346','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','1','1','1','1','1','1','1','1','1','1','1','1','1','1','1','0','1','1','1','1','1','0','0','','','0','','','','04/28/2015')" Error in executing query.

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 8114 [code] => 8114 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to numeric. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to numeric. ) )

27

Okay, starting from the top, I see that the contract status in your is an empty string, but your table schema further up says that it has to be not null. Is that something else that could have a bearing on the case?

28

I have just uploaded a dummy contract that has every option filled in, and the error has changed back to a date issue.

string(1810) "INSERT INTO Intranet (Contract_Number,Contract_Status,Company_Name,Invoice_Street,Invoice_City,Invoice_State,Invoice_Country,Invoice_Postcode,Invoice_Tax_Code,Invoice_Telephone,Invoice_Fax,Invoice_Contact_Name,Invoice_Email,Annual_Contract_Value,Invoicing_Profile,Invoice_Paid_By,Currency_of_Contract,Contract_Start,Contract_End,Renewal_Date,Contract_Length,Quote_Number,Site_Name,Street,City,Country,State,Postcode,Type_of_Establishment,No_of_Rooms,Group_Name,Invoice_Value,Invoice_Frequency,Payment_Contact,Payment_Contact_Tel,Invoice_Currency,Seasonal_or_Full,Month_Opens,Month_Closes,Brandcheck_Frequency,StandardsCheck_Frequency,Roomcheck_Frequency,Foodcheck_Frequency,Crisischeck_Frequency,Poolcheck_Frequency,Firecheck_Frequency,Aquacheck_Frequency,Spacheck_Frequency,Safetycheck_Frequency,Accesscheck_Frequency,Ecocheck_Frequency,Supplycheck_Frequency,Dinecheck_Frequency,Tourcheck_Frequency,Training_Days,Labcheck_Aqua_Frequency,Labcheck_Food_Frequency,Labcheck_Pool_Frequency,Labcheck_Room_Frequency,Legionella_Test_Frequency,No_of_Modules,Additional_Sampling,Principle_Contact,Principle_Telephone,Preferred_Language,Principle_Contact_Job_Title,Principle_Contact_Email,contract_File,date_Created)VALUES('DOM12346','Active','accend','2 swallow drive','caldicot','monmouthshire','Argentina','NP265RD','2536725745','07951178120','12345','lee jones','info@accend4web.co.uk','12345','Monthly in Advance','lee','CAD','08/04/2015','25/04/2015','25/03/2016','23','12345','Accend','2 swallow drive','caldicot','Azerbaijan','monmouthshire','NP265RD','Catering','2','Accor','1234','Monthly in Advance','lee','421412','BRL','1','January','January','2','2','2','2','2','2','2','2','2','2','2','2','2','2','2','1','2','2','2','2','2','20','1','lee','421512','1','developer','info@accend4web.co.uk','','04/28/2015')" Error in executing query.

Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 241 [code] => 241 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. ) )

So with regard to the 4 date entries, they are being uploaded as 25/04/2015 but the database shows it like 2015-04-25, I also just sent an email to the server guy about the NULL as it seems that is its own validation when there no validation on the form, it can be just one field that gets filled in sometimes

29

Now that you have just a date error, try some different date formats., starting with YYYYMMDD

30

I need to come back to this help for a slight change in the script below.

At the moment the renewal date is picking a date that is -1 month from the end date, but I need it changed so that its picking 30 days from the date in end date, rather than the 1 month.

$("#endDate").datepicker({
dateFormat: "yy-m-d",
onSelect: function(dateText, instance) {
    date = $.datepicker.parseDate(instance.settings.dateFormat, dateText, instance.settings);
    date.setMonth(date.getMonth() - 1);
    $("#renewalDate").datepicker("setDate", date);
}
});

So basically

date.setMonth(date.getMonth() - 1);

Needs to get -30 days instead

31

What happens with you try date.getDay() - 30

32

Hi Paul,

Ah ye I tried that, so at least I did give it a go and basically when I date picked the end date to be

2015-6-30

the Renewal date came back as

2012-9-30

That’s when I jumped back on the forum.

Just to add, does that second date relate to minus 30 months perhaps, im not sure.

33

Yes, that certainly looks like 30 months that was happening there. Try checking over your code once more, and if the problem continues, please show us the code that you are using to subtract 30 days.

34

Hi Paul,

Ye that’s the problem, I cant work it out so that it takes 30 days away rather than 30 months.

I’m back to where I was in honesty -

$("#endDate").datepicker({
dateFormat: "yy-m-d",
onSelect: function(dateText, instance) {
    date = $.datepicker.parseDate(instance.settings.dateFormat, dateText, instance.settings);
    date.setMonth(date.getDay() - 30);
    $("#renewalDate").datepicker("setDate", date);
}
});
$("#renewalDate").datepicker({  
dateFormat: "yy-m-d"
});
$("#startDate").datepicker({  
dateFormat: "yy-m-d"
});

var previousData = <?php echo json_encode($_POST); ?>;
for (item in previousData) {
// Check if item is set by you and not is a js property
if (previousData.hasOwnProperty(item)) {
// Set the value to the input with same name
$('input[name='+item+']').val(previousData[item]);
}
}
35

I’ve got it closer but its still out, and doesn’t make sense at all

Changed it too

date.setDate(date.getDay() - 30);

Datepicker end date is
2015-6-29
and renewal date is
2015-5-2

So the month less is correct but what seems to be happen now is that its going to the previous month and then reducing it by 30, rather than reducing it by 30 from the month its already on.

36

Got it
date.setDate(date.getDate() - 30);

Cheers

37

Excellent - I’m glad we were able to inspire you towards that solution.