1

I've added a [DAYS_FROM_TODAY] column to my table to calculate the number of days between today's date and the date a check was written for each record in that table.

I used the following code to update the table:

UPDATE [dbo].[Unpaid]
SET [DAYS_FROM_TODAY] = DATEDIFF(dd, CHECK_DATE, GETDATE())

However when I look at the top 1,000 records of the [dbo].[Unpaid] table, the [DAYS_FROM_TODAY] column has the following appearance instead of integers:

DAYS_FROM_TODAY

1900-09-03 00:00:00.000

1900-01-23 00:00:00.000

Both the CHECK_DATE and DAYS_FROM_TODAY columns are datetime types. Am I missing an important step?

3
  • Why are you doing UPDATE without a WHERE clause ?
    – Kin Shah
    Mar 13, 2015 at 19:18
  • @Kin I need to know how long all unpaid checks that we've sent out to respective organizations, have been outstanding.
    – Corey
    Mar 13, 2015 at 19:31
  • Then it should be a select .. not an update without a where clause. Depending on the table size, it can take a while and can fill up your T-log.
    – Kin Shah
    Mar 13, 2015 at 19:39

1 Answer 1

2

It's converting the integer to a datetime- the column is a datetime.

You're seeing the datetime conversion of the results of the DATEDIFF function. Implicit conversion from integer to datetime is in days beginning with 1/1/1900 as 0.

  SELECT CAST(1 AS DATETIME)

Returns

1900-01-02 00:00:00.000

If you want to store the integer result in the column, just change the data type to integer.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.