Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert varchar to date


convert varchar to date

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
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>Wink = 0

You could then CAST or CONVERT your text data to a date data type

Andrew SQLDBA
Bevan Keighley
Bevan Keighley
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 834
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45143 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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

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

How to post your question to get the best and quick help
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 1721
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





 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search