Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert varchar to date Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 6:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1452889
Posted Tuesday, May 14, 2013 7:16 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:32 PM
Points: 977, Visits: 3,357
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
Post #1452896
Posted Tuesday, May 14, 2013 9:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:43 PM
Points: 298, Visits: 711
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
Post #1452908
Posted Tuesday, May 14, 2013 10:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1452913
Posted Wednesday, May 15, 2013 3:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 2,856, Visits: 5,125
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?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1452986
Posted Wednesday, May 15, 2013 9:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1453196
Posted Wednesday, May 15, 2013 11:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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



 
Post #1453247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse