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.
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 DateofBirthThanks |
|
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.JimEveryday 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), '/', '') |
|
|
|
|
|
|
|