CODEWITHSUNDEEP
sql-serverdatetime
Sreevardhan
Sreevardhan

Reputation: 67

change date format into existing table sql server

I have a datetime value, which is in the form of: '2015-03-15 00:00:00.000'. How can I convert it to a datetime value in 'dd/MM/yyyy' format instead and save it in a SQL Server 2012 database?

Here is my C# code:

SqlCommand cmd = new SqlCommand();                          
cmd.CommandText = "SELECT Convert(nvarchar(max), DATEDIFF(day, '" + sStartDate + "','" + sEndDate + "')+1)";
cmd.CommandType = CommandType.Text;

Upvotes: 1

Views: 1031

Answers (3)

Kittoes0124
Kittoes0124

Reputation: 5080

When you're storing date information in a database then you should use your database's date type. A date is a date, not some arbitrary format. Doing so will allow you to safely rely on all of the nice date functions that are built in to SQL Server and save you from an entire class of problems.

If you find yourself constantly reformating the date into a string for reporting purposes then throw the conversion logic in a view/TVF and use that object as your reporting source.

If your only problem is displaying a datetime as a date then you can use:

select Convert(nvarchar(10), SysDateTime(), 103) as [dd/mm/yyyy];

Upvotes: 0

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

You can easily change the data type of your column from datetime to char(24)/varchar(24). then you can use the following method to change the format of your value into a desired format.

SELECT  RIGHT('0'+ CAST(DATEPART(DAY, '2015-03-15 00:00:00.000') AS VARCHAR(2)),2) + '/'
    + RIGHT('0'+CAST(DATEPART(MONTH, '2015-03-15 00:00:00.000') AS VARCHAR(2)),2) + '/'
    + CAST(DATEPART(YEAR, '2015-03-15 00:00:00.000') AS VARCHAR(4));

Code Analysis:

First we acquire the DAY part of the date using DATEPART built-in function. As this value could have one or two character(s) length, we added a constant '0' to the first of this value, and then cut the last 2 characters from it. So for instance, if the DAY is equal to 24 then after adding a '0' to it's beginning it would changed to 024 and then we cut the last two character, which is 24. But if the DAY is equal to 9, then we add a '0' to it's beginning. So now we have 09, and by cutting the last 2 characters we have 09 again. This can guarantee the dd and MM format.

Note that if your column in the database has datetime or datetime2 data type, then you need to solve this issue in a different way.Read more HERE

Upvotes: 0

Devart
Devart

Reputation: 122002

cmd.CommandText =
    "SELECT CONVERT(VARCHAR(10), DATEDIFF(day, '" + sStartDate + "','" + sEndDate + "')+1, 103)";

MSDN

Upvotes: 2

Related Questions