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 = 'update '+@tablename+' SET INV_DATE = CONVERT(varchar(10),INV_DATE,105)'

exec sp_executesql @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

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.

Please

Peter Leow

Why do you want to do that? The datetime format is only matter to human not the sql server. Just leave it as it is as datatime, then format it on select to the appropriate format or culture that suite the users. Learn why you should not bother How to format a Date or DateTime in SQL Server[^]

Peter Leow http://www.peterleowblog.com/ https://www.amazon.com/author/peterleow

TarunKumarSusarapu

Ok.I can leave it.At least can u please tell me how to convert that field into datetime by using above query.

alter table GL add GL_DATE1 datetime null
update GL set GL_DATE=convert(datetime,getdate(),104)
update GL set GL_DATE1=GL_DATE
alter table GL drop column GL_DATE
SP_RENAME 'GL.GL_DATE1','GL_DATE','column'

Peter Leow

Read on How to get Date Part only from DateTime in Sql Server | SqlHints.com[^]

Peter Leow http://www.peterleowblog.com/ https://www.amazon.com/author/peterleow

Chris Quinn

update GL set GL_DATE=convert(date,getdate())

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

TarunKumarSusarapu

Still getting error 'Conversion failed when converting date and/or time from character string.' while converting by using your query.

Richard Deeming

Which means one of the strings in the source column is not a valid datetime value. Which is why you should never store dates as strings. :) If you're using SQL 2012 or later, you could use TRY_CONVERT[^] or TRY_PARSE[^], which will return NULL for any values it can't convert. Otherwise, you're stuck with converting the values manually. NB: In your example, you should update the new GL_DATE1 column directly, rather than updating the original GL_DATE column and then trying to copy it across. You'll probably also want to use the newer datetime2 type[^], which has a better range than the old datetime type.

alter table GL add GL_DATE1 datetime2(0) null;
update GL set GL_DATE1 = TRY_CONVERT(datetime2(0), getdate(), 104);
select GL_DATE from GL where GL_DATE1 Is Null And GL_DATE Is Not Null;

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

TarunKumarSusarapu

Got an exception 'datetime2' is not a recognized built-in function name' while running the query as you mentioned.

Richard Deeming

So you're using SQL 2005 or earlier? You'll have to stick with datetime in that case.

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