Cdate function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Cdate function

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-13 : 08:58:26

Hi i have a file where the date in it is yymmdd example 070412
I first bring the data into a temp table and the date goes into a nvarchar datatype column.

I then want to move the data into another table where the date field has a data type of datetime.

So i use a Active x script to do this. The problem am having is when i use the the formatDate function to try and format the date here is my code.

'mapping of columns
DTSDestination("Date_Scarico") = formatDate(DTSSource("Col007"))

'Function

Function formatDate(ByVal Value)

' First convert to a date friendly format (yyyy-mm-dd)
Value = Left(Value, 4) + "-" + Mid(Value, 5, 2) + "-" + Mid(Value, 7, 2)

If IsDate(Value) Then ' check to see if its a valid date
formatDate = CDate(Value)
Else
GetDate = Null ' If needed replace with some other appropriate value for invalid dates
End If

End Function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:03:41

Value = Left(Value, 4 2) + "-" + Mid(Value, 5 3, 2) + "-" + Mid(Value, 7 5, 2)

You only have two digits for year, right?

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-13 : 09:10:36

I tried that option am i was getting back the date as this 4/7/2010

Yes all i have in the file is yymmdd example 070405

The results of my test were

4/7/2003
4/7/2010

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-13 : 09:12:43

SQL Server can do this conversion directly without an Active X conversion:


declare @t table (DT varchar(6),DATE datetime)

insert into @t
select
	DT,
	DATE = case when isdate(dt) = 1 then DT else null end
from
	-- test data
	(
	select DT = '070412' union all
	select DT = '010412' union all
	select DT = '000229' union all
	select DT = '010229'
	) a

select * from @t

Results:

DT     DATE
------ -----------------------
070412 2007-04-12 00:00:00.000
010412 2001-04-12 00:00:00.000
000229 2000-02-29 00:00:00.000
010229 NULL

(4 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:16:16

Value = "20" & Left(Value, 2) + "-" + Mid(Value, 3, 2) + "-" + Mid(Value, 5, 2)

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-13 : 09:21:58

Thats gives me mmddyyyy which i changed to
Value = "20"& Left(Value, 2) + "-" + mid(Value, 5, 2) + "-" + Mid(Value,3, 2)

which gives me ddmmyyyy

I'd like to keep it all within the Active X script Michael but thanks for your code as it will come in handy.

Thanks for both your help with this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:24:12

This is not the information you provided in your original post...
You write source were in YYMMDD format already.

Never mind.
Good luck.

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-13 : 09:30:38

Yes your right, but i wanted it to be in ddmmyyyy sorry i did not state that.
Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 12:54:01

I think ISDATE should be used with care like ISNUMERIC

Select ISDATE(2000), ISDATE('2000'),ISDATE('March 1'), ISDATE('March 2006')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources