SQLTeam.com | Weblogs | Forums

Sql Query


#1

How to convert string with format ddmmyyyy to date dd/mm/yyyy or dd-mm-yyyy


#2
DECLARE @str VARCHAR(32) = '18022015';
SELECT CONVERT(DATE, STUFF(STUFF(@str,5,0,'/'),3,0,'/'),103);

The query above converts the string to something that is of data type DATE. I would recommend leaving it at that. Unless there is a compelling reason, don't do any further conversions back into a string. If you do want to convert, you can use the CONVERT functions described on this page.


#3

Also it is better to do formatting with something besides T-SQL. As James said, leave a date as a date, it is easier to work with.


#4

Another option:

select cast(right(@str,4) + substring(@str,3,2) + left(@str,2) as date)

#5

Like the others I always prefer to have the APP do the formatting whenever possible - then the APP has a date in native-format that it can sort/manipulate as-a-date, and not as-a-string.

In SQL 2012 the format() function was added, which might be helpful for date formatting.


#6

No particular need to convert it, that may cause implicit conversions during output that you don't want.

SELECT STUFF(STUFF(column_name, 3, 0, '/'), 6, 0, '/')