How to convert total database columns datetime to date in the format of dd-mm-yyyy

[TarunKumarSusarapu]

I tried this query in sql.But getting errors like

'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'

declare cur cursor for Select Table_Name From INFORMATION_SCHEMA.COLUMNS Where column_name = 'INV_DATE'
declare @tablename nvarchar(max)
declare @sqlstring nvarchar(max)
open cur
fetch next from cur into @tablename
while @@fetch_status=0
begin
--print @tablename

    set [@sqlstring](https://www.codeproject.com/Members/sqlstring) = 'update '+@tablename+' SET INV\_DATE = CONVERT(varchar(10),INV\_DATE,105)'

    exec sp\_executesql [@sqlstring](https://www.codeproject.com/Members/sqlstring)

    fetch next from cur into @tablename
end

close cur
deallocate cur

Please suggest me a solution current format:2016-03-31 00:00:00.000 Required Format:31-03-2016 DataType:DateTime

[OriginalGriff]

This isn't related to C#, so it's in the wrong place. But it's a rookie mistake, and I'll answer it as a result. There is but one solution: change your database. Never store dates in NVARCHAR columns - always use DATETIME or DATE instead. If you use the wrong column datatype it's easy to set up, and lazy to get the user data into - but it always gives total nightmares after that - because the data in your DB is not valid, or is not in a consistent format, or is in a different format from that which SQL expects. Think about it: 10/11/12 is a valid date. But is it 10th Nov 2012, 11th Oct 2012, 12th Nov 2010, or some other value entirely? The only time you can tell is when the user inputs the value, and you use his culture to convert it to a DateTime value - as soon as it arrives in the DB it's too late because you no longer have any idea what date format he used: it could be US: MM/DD/YY, European: DD/MM/YY, or ISO / Japanese YY/MM/DD - and you don't even know that the user is using the same calendar as you so the year could be well different (the Hijri date today is Jumada Al-Awwal 3, 1438)! Or even that he didn't enter "hello, my name is Jackie" which isn't even close to a date. So when you try to convert it to a date at a later time you are almost guaranteed to get errors because the SQL server will try to convert it using it's culture - and generally you don't even know what culture the server is set to! Always use appropriate data types - it may be easier for your code to slam in NVARCHAR all the time, but it wastes huge amounts of effort later fixing up the holes it leaves.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

[TarunKumarSusarapu]

I tried to convert in single table also containing date format in datetime as 2016-03-31 00:00:00.000.

update invINVOICE set INV_DATE =convert(datetime,convert(datetime,INV_DATE ,(105)))

But still got the same error while running the query. I already written script for database to convert all the columns to datetime with a format '2016-03-31 00:00:00.000' Now they want only date format containing dd-mm-yyyy. Please suggest me solution this time.

[OriginalGriff]

You can't. DATETIME fields don't have a format - they are stored as a number of tick since a preset point in time (for SQL Server, that's "ticks since Jan 1st 1753") so quite what you expect two datetime conversions to do I'm not quite sure.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

[CHill60]

Everything @OriginalGriff has said but perhaps use Date instead of DateTime. Anything else should be done in the presentation layer.

[Richard Deeming]

TarunKumarSusarapu wrote:

Now they want only date format containing dd-mm-yyyy.

Store the values in a datetime, datetime2 or date column. That will store the actual date value, without any associated formatting. Date and Time Types[^] Any formatting of the value should be done when you display the date values in your application or report.

• SQL Server Reporting Services and Crystal Reports have built-in support for specifying the format of date columns.

• In a .NET application, pass a standard[^] or custom[^] format string to the DateTime value's ToString method.

  DateTime invoiceDate = row.Field<DateTime>("INV_DATE");
  string displayValue = invoiceDate.ToString("dd-MM-yyyy");

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[TarunKumarSusarapu]

I tried to copy data and changing to DateTime using temp field.But also getting the same error.

alter table GL add GL_DATE1 datetime null
update GL set GL_DATE=convert(datetime,GL_DATE,104)-------->Getting error here
update GL set GL_DATE1=GL_DATE
alter table GL drop column GL_DATE
SP_RENAME 'GL.GL_DATE1','GL_DATE','column'

please give me the advice to copy data from one field to another field using the temp table.

[Pete OHanlon]

You need to take this question over to the database forum. You started off by asking a SQL question here and now you're asking another SQL question.

This space for rent

[TarunKumarSusarapu]

Ok Thank you.I will change the message board. :)