SQL Server to Access

[renenger]

I am importing an Excel file into a table in SQL Server that was created by an AS400 program.

I linked this table in MS Access. The date fields from the SQL Server table are split out. DSREQM is the month and day. DSREQY is the year. When opening the table it looks like this:

DSREQM DSREQY
215 6

I need to be able to convert this is a field to 2/15/06. However I cannot get this to work. For some reason when I execute the query it comes out as 8/02. Here is what I have:

DelDate: Format([DSREQM],"mdd") & "/" & Format([DSREQY],"y")

Any ideas?

 

[Pat Hartman]

The "mdd" and "y" arguments of the Format() function assume that the field being formated is a date field. DSREQM and DSREQY are NOT date fields.

Assuming that DSREQM is 4 characters, fixed, the expression would be:

DellDate:CDate(Left(DSREQM,2) & "/" & Right(DSREQM,2) & "/" & DSREQY)

 

[renenger]

Excel to SQL

Hi everyone. I have a .CSV file that I am importing into a table. I cannot get the date to convert on import. It creates June 5, 1959. I am trying to create a function on data transformation. I am close but I can't quite get it to work. In tblUnitImport.DelDate is datetime. Here is the code I have.

Function Main()
Dim dd
Dim mm
Dim yy
Dim date


mm = CStr(DTSSource("Col001")),1,1
dd = CStr(DTSSource("Col001")),2,2
yy = CStr(DTSSource("Col001"))4,2
date = "0"+ mm + "/" + dd + "/" + "20" + yy
DTSDestination("DelDate") = (date)
DTSDestination("ProjectID") = DTSSource("Col002")
DTSDestination("Phase") = DTSSource("Col003")
DTSDestination("Unit") = DTSSource("Col004")
DTSDestination("Tract") = DTSSource("Col005")
DTSDestination("Release") = DTSSource("Col006")
DTSDestination("UnitPlan") = DTSSource("Col007")
DTSDestination("UnitOpt") = DTSSource("Col008")
DTSDestination("POComp") = DTSSource("Col009")
DTSDestination("PrjFrm") = DTSSource("Col010")
DTSDestination("OrderNo") = DTSSource("Col011")
DTSDestination("OrderStat") = DTSSource("Col012")
DTSDestination("Boxes") = DTSSource("Col013")
Main = DTSTransformStat_OK
End Function

The problem I have is the date reads 21706 on the .csv file. I need it add a 0 if the month doesn't read mm. Cint won't work. I get error messages with that. I am using SQL Server 2000. I am also attaching the .csv file. I am stuck. I have not used SQL Server for about 5 years. I'm sorry if this is frustrating to anyone. If I can get this date to convert and I am ready to go. I have a database that I have to have ready in 5 weeks. This is the only thing holding me up. Any help would be GREATLY appreciated.

Thanks so much,
Brandy

 

[renenger]

Date problem importing from csv

Ok. I have given up on SQL Server. I do not know enough about it to get this done in time. I am trying to import a csv file into an Access table. The first column is a date. However on the csv file the date reads 21706. I get an error table. This is the only column it can't convert on import.

Can someone help me with this?

 

[RV]

Try Pat's suggestion.
If that doesn't work (which I doubt), you need to tell us exactly what datatypes are used in SQL Server for your columns.

RV

 

[Pat Hartman]

This might work:

DellDate:CDate(Left(Format(DSREQM, "000000"),2) & "/" & Mid(Format(DSREQM,"000000"),3,2) & "/" & Right(Format(DSREQM, "000000"),2)