May 14, 2013 at 6:12 pm
I would like to convert a varchar column to datetime.
The varchar column are supposed are be like a date : 2013-05-12
But there may be a wrong date due to user error: like this one 0210-05-12.How can I use a statement to convert all them to datetime and if it is a invalid one like above, make it null.
Thanks
May 14, 2013 at 7:16 pm
First thing, always use the correct data type to store data.
You could query the column and use ISDATE()
UPDATE <TableName>
SET <ColumnName> = NULL
WHERE ISDATE(<ColumnName>) = 0
You could then CAST or CONVERT your text data to a date data type
Andrew SQLDBA
May 14, 2013 at 9:53 pm
Hi,
You can use isdate function along with a case statement as below:
create table #temp
(string varchar(32) null);
insert #temp
select '2012-01-08'
union
select '2012-04-04'
union
select '2013-01-04'
union
select '2013-02-26'
union
select '2013-03-12'
union
select '0210-05-12'
union
select '2010-02-30'
union
select 'blah'
union
select ''
union
select null
select string, ISDATE(string) as is_date, case isdate(string) when 0 then null else CONVERT(datetime, string, 120) end as date
from #temp
Regards,
Bevan Keighley
May 14, 2013 at 10:50 pm
sqlfriends (5/14/2013)
I would like to convert a varchar column to datetime.The varchar column are supposed are be like a date : 2013-05-12
But there may be a wrong date due to user error: like this one 0210-05-12.How can I use a statement to convert all them to datetime and if it is a invalid one like above, make it null.
Thanks
Wow! Deja vu! I believe a couple of other folks have asked this question with that very same bad date. You folks all taking the same course? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2013 at 3:18 am
Jeff Moden (5/14/2013)
sqlfriends (5/14/2013)
I would like to convert a varchar column to datetime.The varchar column are supposed are be like a date : 2013-05-12
But there may be a wrong date due to user error: like this one 0210-05-12.How can I use a statement to convert all them to datetime and if it is a invalid one like above, make it null.
Thanks
Wow! Deja vu! I believe a couple of other folks have asked this question with that very same bad date. You folks all taking the same course? 😉
Are you sure it's other folks? May be it's the same one having groundhog day? :hehe:
May 15, 2013 at 9:50 am
Thanks, I forgot to say the big picture.
This bad data is from an application that use oralce database. The data type is date in Oralce.
I am wondering how the bad data like that got into the table in the first place.
because 0210 is not a valid year.
I am importing data from oralce to SQL server using SSIS.
I cannot fix it the Oralce side. in SSIS, I got a data conversion failed because overflow error in the source input column.
I am trying to avoid this bad record, not sure if there is a is_date() function in Oracle, then I can use the query in the source to filter them out
May 15, 2013 at 12:00 pm
This issue has been discussed before. I've posted a date validation procedure before at Date Validation.
To make it even easier, I'll re-post the code here. These are the two procedures I use for very thorough data validation. These procedures are much more reliable than ISDATE because ISDATE may return different results when converting from a string depending on the DATETIME datatype being converted to.
In these procedures I use SMALLDATETIME to validate non-UMC dates and DATETIMEOFFSET to validate UMC dates. If you know that you will always be using DATETIME or DATETIME2 (or even just TIME) then you will want to use those types in the TRY block.
Also note the DATEFORMAT parameter (default DMY) which allows validation even if the date format is different than the server default. The first procedure is all you need to run, but it calls the second procedure internally. Lots of examples below.
FYI - I had to use a stored procedure for this validation because functions won't let you use operators like SET DATEFORMAT or TRY/CATCH. Bummer.
And oh BTW... EXEC dbo.IsValidDate '0210-05-12' returns a valid date.
InputDate StdDate IsValidDate
0210-05-12 0210-05-12 0:00:00.0000000 1
CREATE PROCEDURE [dbo].[IsValidDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
/* This procedure requires procedure IsValidUMCDate */
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dStdDate SMALLDATETIME
,@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@bIsValidDate BIT
SET @dStdDate = NULL
SET @dUMCDate = NULL
/* Check to see if this is a valid UMC date */
IF OBJECT_ID('tempdb..#CheckUMCDate') IS NOT NULL
DROP TABLE #CheckUMCDate
CREATE TABLE #CheckUMCDate (
[ID] INT IDENTITY(1,1) NOT NULL,
[InputDate] VARCHAR(50) NULL,
[ConvertedDate] VARCHAR(50) NULL,
[IsValidUMCDate] BIT NULL
PRIMARY KEY (ID))
BEGIN TRY
INSERT INTO #CheckUMCDate
EXEC dbo.IsValidUMCDate @sDate
END TRY
BEGIN CATCH
INSERT INTO #CheckUMCDate
SELECT
@sDate AS InputDate
,NULL ConvertedDate
,0 AS IsValidUMCDate
END CATCH
SELECT
@dUMCDate = ConvertedDate
,@bIsValidUMCDate = IsValidUMCDate
FROM
#CheckUMCDate
/* Check using regular SMALLDATETIME datatype */
BEGIN TRY
SET @dStdDate = CONVERT(SMALLDATETIME,@sDate)
SET @bIsValidDate = 1
END TRY
BEGIN CATCH
SET @bIsValidDate = 0
SET @dStdDate = NULL
END CATCH
SELECT
@sDate AS InputDate
,@dStdDate AS StdDate
,@bIsValidDate AS IsValidDate
,@dUMCDate AS UMCDate
,@bIsValidUMCDate AS IsValidUMCDate
/*
EXAMPLES:
EXEC dbo.IsValidDate '01-07-2001'-- Valid date
EXEC dbo.IsValidDate '1/7/2001'-- Valid date
EXEC dbo.IsValidDate '07-01-2001'-- Valid date
EXEC dbo.IsValidDate '7/1/2001'-- Valid date
EXEC dbo.IsValidDate '29-12-2013','DMY'-- Valid date in DMY format
EXEC dbo.IsValidDate '29-12-2013','MDY'-- Invalid date in MDY format
EXEC dbo.IsValidDate '32-12-2013'-- ERROR: Date out of range
EXEC dbo.IsValidDate '29-02-2013'-- ERROR: Not a leap year
EXEC dbo.IsValidDate 'xyz'-- ERROR: Invalid date
EXEC dbo.IsValidDate '1234'-- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'-- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606'-- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'-- Invalid std date/valid umc date
*/
END
CREATE PROCEDURE [dbo].[IsValidUMCDate]
@sDate VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT DMY
DECLARE
@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@ERROR BIT
SET @dUMCDate = NULL
BEGIN TRY
SET @dUMCDate = CONVERT(DATETIMEOFFSET,@sDate)
SET @bIsValidUMCDate = 1
END TRY
BEGIN CATCH
SET @bIsValidUMCDate = 0
END CATCH
SET @ERROR = @bIsValidUMCDate
SELECT
@sDate AS InputDate
,@dUMCDate AS ConvertedDate
,@ERROR AS IsValidUMCDate
/*
EXAMPLES:
EXEC dbo.IsValidUMCDate '01-07-2001'-- Valid date
EXEC dbo.IsValidUMCDate '1/7/2001'-- Valid date
EXEC dbo.IsValidUMCDate '07-01-2001'-- Valid date
EXEC dbo.IsValidUMCDate '7/1/2001-- Valid date
EXEC dbo.IsValidUMCDate '29-12-2013'-- Valid date
EXEC dbo.IsValidUMCDate '32-12-2013'-- ERROR: Date out of range
EXEC dbo.IsValidUMCDate '29-02-2013'-- ERROR: Not a leap year
EXEC dbo.IsValidUMCDate 'xyz'-- ERROR: Invalid date
EXEC dbo.IsValidUMCDate '1234'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.000'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.208'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606'-- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606 +05:30'-- Valid date
*/
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply