0

I am trying to covert date value to datetime in SQL with the following query,which is resulting to invalid value:

select convert(datetime,date_1, 25) from table1;

date_1(of data type date): 2019-12-02

expected o/p: 2019-12-02 00:00:00.000

actual o/p: 1905-06-29 00:00:00.000

Can anyone please tell me the right method of converting date to datetime? and what's wrong with the method I tried?

Vineet Nair
  • 1
  • 5
  • Check your input value. This is working as expected for me. – BenR Jul 09 '21 at 19:31
  • The DATE datatype has a domain that far exceeds that of DATETIME. You will need to adjust for that in some fashion. Start by selecting all rows where date_1 (nice column name btw) < '17530101' (which is the lower domain boundary for datetime). – SMor Jul 09 '21 at 19:38
  • Try `convert(datetime,date_1, 23)` – Charlieface Jul 11 '21 at 00:46

1 Answers1

-1

Worked for me:

select cast(date_1 as datetime)
Lev Gelman
  • 177
  • 8
  • SQL processing is a subject of some predefined rules, so it works for everyone regardless of the person running the statement. If you think this should work, please, describe why; if you have doubts - please, describe what *may be* dependent on *which* criteria. – astentx Jul 09 '21 at 23:40
  • If I remember well it depend on SQL server locale. You can check your setting by running: DBCC USEROPTIONS; – Lev Gelman Jul 10 '21 at 15:30