0

I have a columns called From_date and to_date. The columns have default constraint as getdate() and 9999-12-31 respectively.

But I got something strange after loading data to table.

Instead of default value I am getting value in both the column as 1753-01-01 00:00:00.0000000

Has anyone came across this situation? How to solve this issue?

Here is some of the table DDL

ALTER TABLE [dbo].[mytable] 
ADD CONSTRAINT [df_FMDT_IX] 
DEFAULT (getdate()) FOR [from_date] 
GO 

ALTER TABLE [dbo].[mytable] 
ADD CONSTRAINT [df_TODT_IX] 
DEFAULT ('9999-12-31') FOR [to_date] 
GO

DATATYPE FOR THE COLUMN IS DATETIME2

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Jammie george
  • 27
  • 6
  • Perhaps script out your table DDL and post it here including the default constraint. What data type is the column? What happens if you use `CONVERT(DATETIME,'9999-12-31',120)` instead? – Nick.Mc Apr 26 '16 at 12:39
  • How do you insert your rows? That date value is probably some default in the framework/language you use – Peter Henell Apr 26 '16 at 12:39
  • @Nick.McDermaid: datatype of column is datetime2. ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [df_FMDT_IX] DEFAULT (getdate()) FOR [from_date] GO ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [df_TODT_IX] DEFAULT ('9999-12-31') FOR [to_date] GO Thanks – Jammie george Apr 26 '16 at 12:43
  • @PeterHenell: i am using import-export sql wizard to load table from flat file. format: tab delimited – Jammie george Apr 26 '16 at 12:44
  • So have you established whether that value is from the `default` or from the datafile? Why wouldn't you do that as your very first thing? – Nick.Mc Apr 26 '16 at 12:46
  • @Nick.McDermaid: shall i drop table and re-create it with these check? – Jammie george Apr 26 '16 at 12:49
  • Yes. but **first** establish whether these values are from the default or from data values in the file. In fact drop the constraints altogether and load the file and see if you still get the values. Sounds like it's not the constraint at all. – Nick.Mc Apr 26 '16 at 22:54

1 Answers1

2

That date you're seeing is the minimum date value in SQL.

What is the significance of 1/1/1753 in SQL Server?

I'm assuming someone's entered a zero in that column which will display as the min possible value.

Community
  • 1
  • 1
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • i am loading data from flatfile to sql table using import-export wizard. both the column in the flat file are blank so default value should be there, but instead of default some garbage value appearing in the column. – Jammie george Apr 26 '16 at 12:47
  • When you say blank, is that a zero length string? it's not the same as a NULL value. – Rich Benner Apr 26 '16 at 12:47
  • Then it'll use that zero length string as the data to insert, that's not the same as inserting a NULL value and then using the default value. You'd have to put some sort of error capture to see if the data you're trying to import is a string with a length of less than 1 and replace this with a proper NULL value. – Rich Benner Apr 26 '16 at 12:52
  • is it possible by constraint on the column. please suggest. – Jammie george Apr 26 '16 at 12:56
  • Well, the issue isn't with your constraint, it's with the data import process. You could look to see whether you can edit the data in your import step. Alternatively, the messy approach would be to run an update statement after you've done the import to update these values. – Rich Benner Apr 26 '16 at 13:00
  • You could just not import these columns if they're always the default values. – Rich Benner Apr 26 '16 at 13:04