SQLTeam.com | Weblogs | Forums

Convert date function not working on all dates


#1

I'm using SSMS 2016. I have a long date in a file that I want to convert to a shorter date during data conversion. Some of the records are correct and some are not.
Code:
convert(varchar,c.Date_Established,20) as OpenedDate,

Results are mixed:
2010-09-15 00:00:00.0000000 did not convert
2013-10-18 00:00:00 did convert

Original Values:
2010-09-15 00:00:00.0000000
2013-10-18 00:00:00.0000000
Any help would be appreciated.


#2

what is the datatype for Date_Established? This worked for me, although I would specifically specify the length of the convert varchar

drop table if exists #t
Create table #t (Date_Established varchar(50))

insert into #T values
('2010-09-15 00:00:00.0000000'),
('2013-10-18 00:00:00.0000000')

select convert(varchar(30),Date_Established,20) as OpenedDate
from #T


#3

convert(varchar (20),cast(c.Date_Established as datetime),20) as OpenedDate,


#4

Casting it to a DateTime like that sort of defeats the Convert and will fail in places with different regional settings. First convert the text to a DateTime type based on the string format, then again to format it as the text you want.

Convert(varchar(20),Convert(DateTime2, Date_Established, 20),110) as OpenedDate