SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Comparing Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 03/05/2013 :  12:14:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 03/05/2013 :  12:50:28  Show Profile  Reply with Quote
Does this help?
DECLARE @Foo VARCHAR(30) = '8/18/1942';

SELECT RIGHT('0' + REPLACE(@Foo, '/', ''), 8)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/05/2013 :  12:54:12  Show Profile  Reply with Quote
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
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 03/05/2013 :  12:59:11  Show Profile  Reply with Quote
[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.

Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 03/05/2013 :  13:01:54  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 03/05/2013 :  13:13:39  Show Profile  Reply with Quote
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
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 03/05/2013 :  14:27:46  Show Profile  Reply with Quote
It works really well, Jim. Thanks
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 03/05/2013 :  16:31:55  Show Profile  Reply with Quote
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), '/', '')
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000