SQLTeam.com | Weblogs | Forums

Update sql field date from excel file

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)

thanks in advance

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.

1 Like

Column for DUEDATE is defined as bigint
In SQL it shows like this 1988-02-01 19:03:00. In which SQL ?

How is that even possible?

Shalom

Hi, yosiasz
I'm using special function to show this like a date
system.dbo.tabula_dateconvert(PO.DUEDATE)

without it, todays date shows as big number 17552160
so, how do I convert from regular date to this big number and update SQL?

all lines gives an error

Error converting data type varchar to bigint.

Depends what format you use for your big int date data,

YYYYMMDD?
YYYYDDMM

etc

sounds like you are using some ERP system =>
https://www.priority-software.com/us/

the erp is not relevant because I want to update SQL from excel.
can U write me the update command from SQL?

the erp is not relevant because I want to update SQL from excel.
can U write me the update command from SQL

Not until you answer the question you were asked

Can you post the definition of the system.dbo.tabula_dateconvert function please?

And did someone actually create a database called "system"???

if you referring to date format, YYYYMMDD, I think, I live in Israel

Doesnt matter where you live or what you think, what does the data in he database look like

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.

for today's date, database shows 17553600

sorry, here

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[tabula_dateconvert](@per bigint)

returns smalldatetime as

begin

return convert(smalldatetime, (@per + (case when @per > 47862720 then -6312960 else 46283040 end)) / 1440.0)

end

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.

The simplest format would be:

Select datediff(minute, 0, @myDate) - 46283040

The problem with that function is that it 'rolls' the values back to 1979-01-01 00:01:00 when the value exceeds 47862720.

Declare @per bigint = 47862721
 Select convert(smalldatetime, (@per + (case when @per > 47862720 then -6312960 else 46283040 end)) / 1440.0);

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.

and for the erp system. as you see it's blank

So what is the purpose of you changing it via a sql script and not using the UI of the ERP?

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