July 3, 2018 at 8:27 pm
Hello,
I have a excel document that contains 4 digit numbers in a column. Sometimes these numbers come in as dates. In this case, I would just right click and format excel cell to number to fix it. How can I fix this in SQL? For example change 3/20/1915 (varchar) to 5558 (varchar) using SQL.
July 4, 2018 at 12:41 am
Assuming that your excel source does not have as base date 1904 the following will be a good basis for what you need
including a bit of explanation https://stackoverflow.com/questions/19721416/formula-to-convert-date-to-number
declare @tbl table
( input varchar(20)
)
insert into @tbl
select '1/0/1900' union all
select '1/1/1900' union all
select '1/2/1900' union all
select '1/7/1900' union all
select '2/27/1900' union all
select '2/28/1900' union all
select '2/29/1900' union all
select '3/1/1900' union all
select '3/2/1900' union all
select '0' union all
select '1' union all
select '4' union all
select '7' union all
select '57' union all
select '58' union all
select '61' union all
select '62'
select input
, case
when input = '' then cast(null as int)
when input not like '%/%' then convert(int, input)
when input = '1/0/1900' then 0
when input = '2/29/1900' then 60
when input like '%/%/%'
then datediff(day, convert(date, '18991231', 112), convert(date, input, 101))
+ case when convert(date, input, 101) > convert(date, '19000228', 112) then 1 else 0 end
else null
end
from @tbl
July 5, 2018 at 7:04 am
SQL introduced Try_Cast in 2012. Leaves out any hard coding
select input,
Case when try_cast(input as Date) is null
then case when try_cast(input as int) is null
then '0'
else cast(input as nvarchar(20))
end
else cast(datediff(day, convert(date, '18991231', 112), convert(date, try_cast(input as Date), 101))
+ case when convert(date, input, 101) > convert(date, '19000228', 112) then 1 else 0 end as nvarchar(20))
end ConvertedValue
from @tbl
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2018 at 11:22 am
Mike01 - Thursday, July 5, 2018 7:04 AMSQL introduced Try_Cast in 2012. Leaves out any hard coding
select input,
Case when try_cast(input as Date) is null
then case when try_cast(input as int) is null
then '0'
else cast(input as nvarchar(20))
end
else cast(datediff(day, convert(date, '18991231', 112), convert(date, try_cast(input as Date), 101))
+ case when convert(date, input, 101) > convert(date, '19000228', 112) then 1 else 0 end as nvarchar(20))
end ConvertedValue
from @tbl
yes it has try_convert - but your code does not give the same results as mine, neither will it work correctly for all input values higher than 999
July 5, 2018 at 11:57 am
frederico_fonseca - Thursday, July 5, 2018 11:22 AMMike01 - Thursday, July 5, 2018 7:04 AMSQL introduced Try_Cast in 2012. Leaves out any hard coding
select input,
Case when try_cast(input as Date) is null
then case when try_cast(input as int) is null
then '0'
else cast(input as nvarchar(20))
end
else cast(datediff(day, convert(date, '18991231', 112), convert(date, try_cast(input as Date), 101))
+ case when convert(date, input, 101) > convert(date, '19000228', 112) then 1 else 0 end as nvarchar(20))
end ConvertedValue
from @tblyes it has try_convert - but your code does not give the same results as mine, neither will it work correctly for all input values higher than 999
I don't understand > 999?? What were you expecting?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2018 at 12:10 pm
declare @tbl table
( input varchar(20)
)
insert into @tbl
select '1/0/1900' union all
select '1/1/1900' union all
select '1/2/1900' union all
select '1/7/1900' union all
select '2/27/1900' union all
select '2/28/1900' union all
select '2/29/1900' union all
select '3/1/1900' union all
select '3/2/1900' union all
select '0' union all
select '1' union all
select '4' union all
select '7' union all
select '57' union all
select '58' union all
select '61' union all
select '999' union all
select '1000' union all
select '2007' union all
select '5555' union all
select '62'
select *
from (
select input
, case
when input = '' then cast(null as int)
when input not like '%/%' then convert(int, input)
when input = '1/0/1900' then 0
when input = '2/29/1900' then 60
when input like '%/%/%'
then datediff(day, convert(date, '18991231', 112), convert(date, input, 101))
+ case when convert(date, input, 101) > convert(date, '19000228', 112) then 1 else 0 end
else null
end as correct_value
, Case when try_cast(input as Date) is null
then case when try_cast(input as int) is null
then '0'
else cast(input as nvarchar(20))
end
else cast(datediff(day, convert(date, '18991231', 112), convert(date, try_cast(input as Date), 101))
+ case when convert(date, input, 101) > convert(date, '19000228', 112) then 1 else 0 end as nvarchar(20))
end incorrect_ConvertedValue
from @tbl
) t
where t.incorrect_ConvertedValue <> t.correct_value
July 8, 2018 at 8:25 am
clicky - Tuesday, July 3, 2018 8:27 PMHello,I have a excel document that contains 4 digit numbers in a column. Sometimes these numbers come in as dates. In this case, I would just right click and format excel cell to number to fix it. How can I fix this in SQL? For example change 3/20/1915 (varchar) to 5558 (varchar) using SQL.
Do you have any business rules for the acceptable date range?
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply