Comparing Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Comparing Dates

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2013-03-05 : 12:14:57

I have two columns of data which are dates that I need to compare but only one of them is stored properly in date format.

Column A varchar(8)
Sample data: '08181942', '12191980'

Column B datetime
Sample data: '8/18/1942','12191980'

I want to see if the two values are equal. How do I convert or cast Column B as a string with the leading zero if needed?

I have this but it does not add the leading zero.

REPLACE(CONVERT(VARCHAR(10), ColumnB, 101), '/', '') AS DateofBirth

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-05 : 12:50:28

Does this help?

DECLARE @Foo VARCHAR(30) = '8/18/1942';

SELECT RIGHT('0' + REPLACE(@Foo, '/', ''), 8)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-05 : 12:54:12

ColumnB can't be datetime if you say have values of '8/18/1942' and '12191980' in it.

Jim

Everyday I learn something that somebody else already knew

evanburen
Posting Yak Master

167 Posts

Posted - 2013-03-05 : 12:59:11

[quote]Originally posted by jimf

ColumnB can't be datetime if you say have values of '8/18/1942' and '12191980' in it.

Sorry, you're right. They are both varchar columns and the values are 8/18/1942 and 12/19/1980.

evanburen
Posting Yak Master

167 Posts

Posted - 2013-03-05 : 13:01:54

quote:
Originally posted by Lamprey

Does this help?
DECLARE @Foo VARCHAR(30) = '8/18/1942';

SELECT RIGHT('0' + REPLACE(@Foo, '/', ''), 8)

Yes, it does, but now I'm seeing the leading zero in the mm place also being dropped.

9/9/1957 should 09091957 and not 0991957

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 13:13:39

Here is another way to fix it, but I am not hopeful that this would fix it all, because there may be dates in other formats and other variations:

DECLARE @Foo VARCHAR(30) = '8/18/1942';

SELECT 
	CASE 
		WHEN @Foo LIKE '%/%' THEN 
			RIGHT('0'+PARSENAME(REPLACE(@Foo,'/','.'),3),2)+
			RIGHT('0'+PARSENAME(REPLACE(@Foo,'/','.'),2),2)+
			PARSENAME(REPLACE(@Foo,'/','.'),1)
		ELSE @Foo
	END AS FixedUpDate

evanburen
Posting Yak Master

167 Posts

Posted - 2013-03-05 : 14:27:46

It works really well, Jim. Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-05 : 16:31:55

Here is another way to do it too:

DECLARE @Foo VARCHAR(30) = '8/18/1942';

SELECT REPLACE(CONVERT(VARCHAR(10), CONVERT(DATE, @Foo, 101), 101), '/', '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources