0

For years, I have used SSIS (SQL Server Integration Services) to import flatfile data into SQL Server tables. In the event that time-only string fields are cast to datetime fields, I typically see a conversion like this:

"13:45" to "1899-12-30 13:45:00.000"

Just by chance I noticed that if I do this in a T-SQL query using SSMS (SQL Server Management Studio), this happens:

select cast('13:45' as datetime)` produces "1900-01-01 13:45:00.000"

I am curious why two different date values appear. If it's all SQL Server, shouldn't the same "null date value" be the same?

RandomHandle
  • 641
  • 7
  • 25

1 Answers1

0

You're comparing apples to oranges. In one, you're probably using OleDb to connect to a datasource, in the other your running a native query.

In the documentation for OleDb you'll see that depending on the settings it will either convert a time to a DateTime with the current date or a date of 1899-12-30.

The SQL Server documentation explains how it simply puts '1900-01-01' in front when converting from time data.

Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70