Columns in my table is DATE format
I am uploading data in DD-MM-YYYY format
Using the below query:
SET DATEFORMAT DMY;
BULK INSERT stg.leaveapplication FROM 'qa-1/8/testdashboard/Leave dashboard/DatewiseLeaveRoster.csv'
WITH (DATA_SOURCE = 'AzureBlobStorage',
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0A');But my date columns are populating like 2023-04-03T00:00:00.0000000 and blank columns are populating like 1900-01-01T00:00:00.0000000
I was expecting my values to be loaded in DD-MM-YYYY format and blank value where it is blank. But I am getting 2023-04-03T00:00:00.0000000 and blank columns are populating like 1900-01-01T00:00:00.0000000
1条答案
按热度按时间omvjsjqw1#
Datecolumns in SQL Server NEVER preserve any kind of human-readable format. Any original format provided at insert/update time is always lost. This is a good thing. Instead, these columns store values in a compact binary format that is efficient for storage, memory use, indexing, and date math operations.Any other format you see, such as the
2023-04-03T00:00:00.0000000value from the question, is a convenience provided by your tooling. If the format matters, then specify that format again at the point when you query the data (hint: you're usually better off letting the client code or reporting tool worry about the format, and just return the raw value).The one thing that is surprising is seeing
1900-01-01for the blank values. Typically, I would expect a databaseNULLhere. Again, this could be an artifact of the tool you're using to view the data.