convert string data to datetime

[sparkyrose]

Hi,

I have some date data in one Db in the non-date format yyyymm. I need to copy some of it on a regular basis to another table where the destination column data type is Datetime. I'm trying to use a scheduled DTS Package to perform this task with the following code:

SELECT [Acct], [ExtRefNbr], Substring ([PerPost],5,2) + '01' + Substring ([PerPost],1,4) as Perpost
FROM ....etc

The statement works fine and produces the data (by which I mean that 200802 becomes 02012008), but when I try to run the package it fails.

I think it's something to do with putting non-date data into a datetime column but can't work out how to fix that (I'm kind of a novice at this

Any ideas are greatly appreciated!

 

[r937]

try SUBSTRING(PerPost,1,4)+'-'+SUBSTRING(PerPost,5,2)+'-01'

r937.com | rudy.ca

 

[ESquared]

It's good practice to be careful about localization and the order of month vs. day values. So my recommendation is to use the format 'yyyymmdd' with no other characters:

SELECT
   Acct,
   ExtRefNbr,
   CASE WHEN IsDate(PerPost + '01') = 1 THEN PerPost + '01' ELSE NULL END as Perpost

If you're finding that you DO get null values, then insert the data to a table that has a varchar() column instead of a datetime column, and then

SELECT DateCol FROM TempTable WHERE IsDate(DateCol) = 0

Once you see actual examples of the bad data, then you can devise a plan to handle it.

[COLOR=black #e0e0e0]"Step away from the database and put your hands on top of the monitor. Don't make any sudden moves." - busting real life serious database misuse!
Visit my blog for SQL help and general technical ideas.[/color]

 

[sparkyrose]

Hi,

Had a delay on this problem; thanks for the replies. Unfortunately neither of the above suggestions worked in our environment. The DTS Package preview produces the right data, but when the package runs it fails. The original data type is String and the destination is Datetime.

Would it make sense to try and use Cast or Convert to convert the data to Datetime in the Select statement?

Thanks again

 

[gmmastros]

>> Would it make sense to try and use Cast or Convert to convert the data to Datetime in the Select statement?

You'd think so. In this case, it doesn't matter because when you try to assign a varchar value to datetime value, SQL Server will automagically convert this for you.

The following code snippet demonstrates this.

Declare @Test DateTime

Set @Test = '20080507'

Select @Test

It would have been handy to know what error message you are getting now.

 

[ESquared]

If your DTS package is failing then troubleshoot it. I already gave you hints about how to do that.

Create a new DTS package just like your first one but stash the date values in a varchar column instead of a datetime column. Run the package. Query the destination table directly to find values that aren't IsDate() = 1. Correct problem.

Rinse.

Repeat.

 

[sparkyrose]

Thanks for the replies.

The error message is as follows:

"The number of failing rows exceeds the maximum specified.
TransformDateTimeString 'DTSTransformation_7', column pair 1(source column 'Perpost' (DBTYPE_STR), destination column 'Datepaid' (DBTYPE_DBTIMESTAMP)): Cannot parse data string beginning at '20080401'.

This was received when running ESquared's
CASE WHEN Isdate...etc code.

I'll try and do a proper troubleshooting later today and see what it turns up.

Thanks

 

[SQLSister]

Are you sure the column is a datetime? Looks like the transform is trying to transform it to a TImestamp.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

A timestamp data type is completely different from a datetime data type (in fact, timestamp has little to do with time).

The exact error message was so enlightening!! Shock and amazement!

 

[SQLDenis]

however just to complicate matters in SSIS
DT_DBTIMESTAMP is a datetime not a timestamp


DT_DBTIMESTAMP
A timestamp structure that consists of year, month, day, hour, minute, second, and millisecond.

a timestamp would be DT_BYTES

DT_BYTES
A binary data value. The length is variable and the maximum length is 8000 bytes.



more here:

http://msdn.microsoft.com/en-us/library/ms141036.aspx

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I was just reading about this the other day.

timestamp

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. [COLOR=black yellow]Use rowversion instead of timestamp wherever possible in DDL statements.[/color] rowversion is subject to the behaviors of data type synonyms.

 

[sparkyrose]

So I tried troubleshooting and there is no dodgy data.

It made me think, however, maybe a better solution would be to add the data to a 'holding' column as varchar and then make a computed column with a user-defined function to convert it to Datetime.

So my UDF is:

CREATE FUNCTION DateConvert (@Perpost varchar)
returns Datetime
as
begin
return convert(Datetime, @Perpost, 112)
end

Then I ran the following:

alter table [table name] add Datepaid as dbo.DateConvert(Perpost)

Perpost is the 'holding' column name.

The DTS package runs fine, and the table is showing the correct # of rows, but when I try to view the data I get the following error message:

"Syntax error converting datetime from character string."

Any ideas? As always, thanks for your thoughts!

 

[SQLDenis]

>>varchar

varchar what? varchar(10) varchar(50)

where is the length?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sparkyrose]

Such a little thing!

It all works fine now I think.

Thanks to everyone who offered their thoughts!

 

[ESquared]

If it works this way, it should have worked to insert directly to datetime.

 

[furtivevole]

Hi

This is a very basic question needing a quick-fix answer, as I am fairly new to T-Sql

I am using a pre-existing SQLServer 2000 database including a user-defined function requiring a datetime input:

CREATE FUNCTION [dbo].[fnMyFunction] (@i as int, @j as int, @CurrentDate as datetime) ... etc

In an asp page, I need to call this function with vbscript but am having trouble handling the datetime type within the SELECT statement. Could anyone give me an example? Thanks.