| Author |
 Topic  |
|
|
evanburen
Posting Yak Master
128 Posts |
 Posted - 03/05/2013 : 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
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 03/05/2013 : 12:50:28
|
Does this help?DECLARE @Foo VARCHAR(30) = '8/18/1942';
SELECT RIGHT('0' + REPLACE(@Foo, '/', ''), 8) |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 03/05/2013 : 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
128 Posts |
Posted - 03/05/2013 : 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
128 Posts |
Posted - 03/05/2013 : 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 03/05/2013 : 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
128 Posts |
Posted - 03/05/2013 : 14:27:46
|
| It works really well, Jim. Thanks |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 03/05/2013 : 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), '/', '') |
|
|
| |
Topic |
|
Comparing Dates
