JDCam
asked on
Insert DATETIME to SQL Server
Experts,
Seems like a very simple thing, yet I have been stuck on this for a while.
As part of an insert I am trying to write CURRENT_TIMESTAMP into a SQL Server DATETIME column. When I select back the inserted record, the date is correct but the time is missing and appears like '2021-07-17 00:00:00.000'
I am currently using a mask of 'yyyy-MM-dd HH:mm:ss'. the same mask used in a select looks fine.
What am I doing wrong?
Seems like a very simple thing, yet I have been stuck on this for a while.
As part of an insert I am trying to write CURRENT_TIMESTAMP into a SQL Server DATETIME column. When I select back the inserted record, the date is correct but the time is missing and appears like '2021-07-17 00:00:00.000'
I am currently using a mask of 'yyyy-MM-dd HH:mm:ss'. the same mask used in a select looks fine.
What am I doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Still works for me:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a2c5e7ebe47572d5210ae715cde9cb58
create table bob(col1 datetime);
insert into bob values('2021-07-14 11:28:32.463');
select * from bob;https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a2c5e7ebe47572d5210ae715cde9cb58
ASKER
I agree.. On the destination server I can update/Insert the records the same and they look fine.
Something goofy is going on this external insert.
I wonder if something would be visible in any of the server logs. Is there any such thin like an NLS setting on the connection used?
Something goofy is going on this external insert.
I wonder if something would be visible in any of the server logs. Is there any such thin like an NLS setting on the connection used?
EXPERT CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@JDCam: Open SSMS right click the table and click on the "Script table as ...". You may post the script here. Additionally check whether some triggers are defined on that table
@JDCam: You wrote "currently using a mask of 'yyyy-MM-dd HH:mm:ss'. the same mask used in a select looks fine."
Could you please show how do you use that mask in the SELECT statement?
Could you please show how do you use that mask in the SELECT statement?
ASKER
I confirm the column is DATETIME and not DATETIME2
@JDCam: Any estimation on table creation script, possible triggers, and the formatting in SELECT?
ASKER
There are no triggers attached to the table.
Create script is below.3rd column iasn_RecordDate is the focus
Create script is below.3rd column iasn_RecordDate is the focus
USE [LVSDB]
GO
/****** Object: Table [dbo].[CUS_IMP_EXP_REC] Script Date: 7/15/21 7:50:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUS_IMP_EXP_REC](
[iasn_RecordID] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[iasn_RecordStatus] [varchar](10) NOT NULL,
[iasn_RecordDate] [datetime] NOT NULL,
[iasn_RecordError] [varchar](255) NULL,
[iasn_ExpRecCode] [varchar](32) NOT NULL,
[iasn_ExpRecType] [varchar](3) NOT NULL,
[iasn_LogisticSiteCode] [varchar](10) NOT NULL,
[iasn_ClientCode] [varchar](10) NOT NULL,
[iasn_POCode] [varchar](20) NULL,
[iasn_SupplierCode] [varchar](20) NULL,
[iasn_SupplierName] [varchar](150) NULL,
[iasn_SupplierAddress] [varchar](150) NULL,
[iasn_SupplierArea] [varchar](30) NULL,
[iasn_SupplierCity] [varchar](32) NULL,
[iasn_SupplierCountry] [varchar](30) NULL,
[iasn_SupplierState] [varchar](30) NULL,
[iasn_SupplierZipCode] [varchar](10) NULL,
[iasn_ReferenceDoc] [varchar](64) NULL,
[iasn_ExpectedDate] [datetime] NULL,
[iasn_CarrierCode] [varchar](32) NULL,
[iasn_CarrierName] [varchar](128) NULL,
[iasn_CarrierProBill] [varchar](128) NULL,
[iasn_REF_01] [varchar](128) NULL,
[iasn_REF_02] [varchar](128) NULL,
[iasn_REF_03] [varchar](128) NULL,
[iasn_REF_04] [varchar](128) NULL,
[iasn_REF_05] [varchar](128) NULL,
[iasn_EDI_REF_H01] [varchar](128) NULL,
[iasn_EDI_REF_H02] [varchar](128) NULL,
[iasn_EDI_REF_H03] [varchar](128) NULL,
[iasn_EDI_REF_H04] [varchar](128) NULL,
[iasn_EDI_REF_H05] [varchar](128) NULL,
[iasn_LineNo] [numeric](10, 0) NULL,
[iasn_ItemCode] [varchar](40) NOT NULL,
[iasn_UnitCode] [varchar](10) NOT NULL,
[iasn_Quantity] [numeric](10, 0) NOT NULL,
[iasn_LPN] [varchar](26) NULL,
[iasn_LotNo] [varchar](128) NULL,
[iasn_ExpDate] [datetime] NULL,
[iasn_ProdDate] [datetime] NULL,
[iasn_EDI_REF_D01] [varchar](128) NULL,
[iasn_EDI_REF_D02] [varchar](128) NULL,
[iasn_EDI_REF_D03] [varchar](128) NULL,
[iasn_EDI_REF_D04] [varchar](128) NULL,
[iasn_EDI_REF_D05] [varchar](128) NULL
) ON [PRIMARY]
GO
ASKER
The outputted insert statement from the source server must be malformed. In its application logs it looks fine. On the destination SQL server (2019) is there a transaction log that would show me the syntax it received and is attempting to execute?
ASKER
Issue is resolved... It was an issue in the source application that has been resolved by the vendor.
Thank you everyone for your assistance
Thank you everyone for your assistance
ASKER
In the log I can see the format as
Open in new window
when I select back the same record it displays as '2021-07-14 00:00:00.000'