Menu
SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS.
We can convert the Date into Datetime in two ways. Using CONVERT() function: Convert means to change the form or value of something. The CONVERT() function in the SQL server is used to convert a value of one type to another type. Convert() function is used to convert a value of any type to another datatype.
The DATEVALUE function in Excel converts a date in the text format to a serial number that Excel recognizes as a date. So, the formula to convert a text value to date is as simple as =DATEVALUE(A1) , where A1 is a cell with a date stored as a text string.
I have found something really weird today while doing a work converting a datetime to text in excel and using the number generated by it to convert to datetime in SQL Server.
What is weird about it? Different results. Two days difference to be precise.
I assumed the date of today (20/05/2014 dd/MM/yyyy ) in Excel and got 41779 as result in text.
I got the text value and I use SQL convert to datetime to retrieve the value as date and I did not get the result I wanted.
I even tested with datetime2 but I learned that I can't convert int to datetime2
I'm not a MS Excel expert nor a SQL Server expert, but what is going on? I can make it work by doing the number generated by MS Excel and removing 2, but still doesn't make sense to me.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With