I'm trying to write a query to update a different date in sql without success.
getting all kind of errors with the conversion types.
I live in Israel, so the my date format is like DD/MM/YYYY, althought in SQL it shows like this 1988-02-01 19:03:00.
I need something like this.
UPDATE PORDERITEMS SET DUEDATE = 15/06/21
the column for DUEDATE is defined as bigint (if it helps)
once I'll get it done, the goal is connecting excel to SQL, pulling some records, changing the dates for some of those records
and write VBA code that will update the SQL with the new dates. (the connection to excel and the VBA already done, just missing the syntax for changing the dates)
Why are you trying to store a date as a bigint? It should be defined using the date data type - or datetime if you need the time component.
Then - for updating you always want to specify an un-ambiguous format. For the date data type you have the ISO formats of YYYYMMDD or YYYY-MM-DD and for the datetime data type you have YYYYMMDD. For a full ISO datetime format you can use either: YYYY-MM-DDTHH:MM:SS or YYYYMMDDTHH:MM:SS (the T is hard-coded into the string, for example: 1988-02-01T19:09:00)
These formats are not impacted by language settings in SQL Server and will always be interpreted correctly.
When you say it shows as YYYY-MM-DD HH:MM:SS in SQL Server - that tells me it is actually a datetime data type and not a bigint. The update statement would then be one of the following:
UPDATE PORDERITEMS SET DUEDATE = '20210615 00:00:00.000';
UPDATE PORDERITEMS SET DUEDATE = '20210615T00:00:00.000';
UPDATE PORDERITEMS SET DUEDATE = '2021-06-15T00:00:00.000';
If the DUEDATE is a datetime2 data type - or datetimeoffset - the only difference is that both of those will interpret YYYY-MM-DD HH:MM:SS correctly.
BTW. The reason why I'd like you to post the function you spoke about is because it contains the code to do the conversion one way and we can simply use that code as an example of how to reverse engineer the code to convert a normal date to the bigint. I've tried a couple of things with many of the standard epochs for such conversions and none of the seem to fit for year although they seemed to fit for month and day.
We need the code for that function to figure this out.
Look for another function in that database - there should be the opposite function to convert from a smalldatetime to a bigint. That would be the easiest way to resolve this issue.
For a function with an input parameter of smalldatetime - the max value that can be utilized is 2079-06-06, but the max value of the above function is limited to 2079-01-01.
Yes, I know the date limitation and the syntex works bu know I have another problem.
my ERP system not seeing this change. only for the dates - very strange. although the database updates correctly. attaching some pictures
I've changed the quantity and the system seeing it.
I want to update from excel, because I have a lot of lines, and for that I wrote some VBA code.
what was missing was the SQL command and it's working but the ERP for some reason not updating accordingly