SQL Server 2008 – How to Convert Date and Time

sql-server-2008

I have a column with about 100 records in datetime (for example 2004-08-09 12:20:41:000) and I would like to convert all records or update same time just date only with format mm-dd-yyyy.

Thank you.

Best Answer

Try converting the datetime column as follows:

UPDATE [TableName]
    SET DateCreate = CAST(LEFT(CONVERT(NVARCHAR(25), DateCreate, 120), 10) AS DATETIME)

Related Solutions

Sql-server – Date Format in Sql Server

There is no way to distinguish the meaning of 01/02/2011 and 02/01/2011 if you have this values mixed in the same column. It is that simple.

Sql-server – Storing date as integer (numeric), what are the advantages

To answer your first question, I would recommend using the DATETIME data type within SQL Server. Not necessarily for performance reasons, but to leverage the RDBMS-specific functionality. For instance, you would have to re-invent a lot of logic just to do basic date math (think DATEDIFF(), DATEADD(), DATEPART() and many other functions. They are obviously tailored to the DATETIME data type and are easy to work with).

As for your second question, you are running into the exact problem that the first question (and my answer) is geared towards. You are looking at 20111201 and 20120131 as dates, and your brain is telling you that should be a difference of 60 days. Well, you're looping through based off of the delta...which is:

20120131 - 20111201 = 8930 (with the inclusive loop it'll be 8931)

In other words, your WHILE loop is executing 8931 times. This is happening because those are integer values and your loop will not jump from 20111231 straight to 20120101.

You integers aren't going to take into account the cap of years and months (i.e. your Question 2 problem).

Best Answer

Related Solutions

Sql-server – Date Format in Sql Server

Sql-server – Storing date as integer (numeric), what are the advantages

Related Question