converting date format

[wizard1923]

just need help converting date from yyyymmdd to yyyy-mm-dd so i can compare two fields with different date formats

 

[markros]

Are you using varchar for your dates? If so, take a look at the STUFF function in BOL.

PluralSight Learning Library

 

[ousoonerjoe]

Take a look at CAST and CONVERT:

SELECT [Date1] = CONVERT(DATE, '17760704', 101)
SELECT [Date1] = CAST('17760704' AS DATE);

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[sparkbyte]

I think this may be what you are looking for.

Declare @MyTable Table (
	PK int,
	COLUMN1 Varchar(10),
	COLUMN2 Varchar(10),
	DATE_COLUMN1 VarChar(10),
	DATE_COLUMN2 VarChar(10) )

Insert INTO @MyTable (PK, COLUMN1, COLUMN2,DATE_COLUMN1, DATE_COLUMN2 )
Values (1,'John','Smith','01-01-2011','20110115')

Insert INTO @MyTable (PK, COLUMN1, COLUMN2,DATE_COLUMN1, DATE_COLUMN2 )
Values (2,'Terry','Kriten','02-01-2011','20110210')

Insert INTO @MyTable (PK, COLUMN1, COLUMN2,DATE_COLUMN1, DATE_COLUMN2 )
Values (3,'Merry','Xmas','05-17-2011','20110520')

SELECT
	PK,
	COLUMN1,
	COLUMN2,
	CAST(DATE_COLUMN1 AS DATETIME),
	CAST(DATE_COLUMN2 AS DATETIME),
	DATEDIFF(DAY,DATE_COLUMN1,DATE_COLUMN2) As 'Difference in Days'
FROM @MyTable
WHERE DATEDIFF(DAY,DATE_COLUMN1,DATE_COLUMN2) > 10

Thanks

John Fuhrman