CODEWITHSUNDEEP
sql-servert-sqldatetime
slayer35
slayer35

Reputation: 604

Format a string type into a datetime format

i have a column (nvarchar),there are datetimes in there but as a string.I want to change that column's type into smalldatetime but before i want to format all string dates into a same datetime format.But i cant succeed it.Some of them are like this "2007-07-10",some of them are reverse of this "02.07.2008". How can i change them into one format for example dd.mm.yyyy. (ms sql server 2008)

Upvotes: 1

Views: 177

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

If you only have those 2 formats, this is how you can do it.

I am assuming

'02.07.2008' has format 'dd.mm.yyyy'

'2007-07-10' has format 'yyyy-mm-dd'

If you have more formats than that, you have to alter the 'case' in the 'select' clause accordingly

declare @t table (a varchar(12))

insert @t values ('02.07.2008')
insert @t values ('2007-07-10')

select convert(date, a, case when charindex('-',a) = 5 then 21 else 103 end)
from @t

Output

2008-07-02
2007-07-10

The format is standard for a date field, but if you want to store it as a varchar, you can format it as dd.mm.yyyy like this instead.

select replace(convert(varchar, convert(date, a, case when charindex('-',a) = 5 
then 21 else 103 end), 103), '/', '.')
from @t

Output

02.07.2008
10.07.2007

I have to point out that you should always store a date as a date and not a varchar in the database when possible.

Upvotes: 2

Petar Ivanov
Petar Ivanov

Reputation: 93000

You can't do it unless you know the exact format.

Think about the different formats - in some countries the month comes first, while in the other it's the day. So if you don't know whether 02.07.2008 means July 2th, or it means Feb 7th, then you can't possibly accomplish your task.

Upvotes: 2

Related Questions