date need back off four year

I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.You can use the expression DateAdd(year, -4, GetDate()) in order to find the date four years ago. Without knowing a lot more about your table structures, etc. I can't make a good guess at what code you'll need.

-PatP|||thanks pat, i am using DTS and schedule to import the table to another database every night. My table has fields: Name, Address, County, QualityDate. Quality is short date type. Is that good for you to figure out when i create job how to write a query in where clause, such as, select Name, Address, County, QualityDate from table1 where ...... (i don't know how to do it) .Thanks.|||This won't be absolutely perfect, but you could get really close using:SELECT Name, Address, County, QualityDate
FROM SourceServer.SourceDatabase.dbo.SourceTable
WHERE QualityDate
BETWEEN Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121)
AND Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121) + ' 23:59'That snippet will pick up the rows that occured anytime on the day that is four years ago today. This should work Ok for 90+ years, which will be well past the point that SMALLDATETIME can represent!

-PatP|||thanks pat, i got it. Have a nice day!

Date Manipulation

I originally posted this in the DTS forum, but then thought this particular
issue is more "programming" than "DTS" oriented...so I am not multi posting.
I've also changed the code slightly (found a couple of glaringly obvious
errors!)
I need to write a DTS package which is date oriented and I'm having problems
getting something coherent. I'm not a big guy in SQL Server, so please excus
e
my ignorance.
I need to get some sales information based on the previous day...however, if
the previous day was Monday, then I need to get the sales data for the
Friday. The where clause looks like this (and it's not
working...unsurprisingly).
/ ****************************************
*
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dd,getdate())) = 'MONDAY'
then
dateadd(dd,-3,getdate())
--and ididat < 20060320
else
dateadd(dd,-1,getdate())
--and ididat < 20060320
end
/ ****************************************
*
The error I get is
Arithmetic overflow error converting expression to data type datetime.
The IDIDAT field in the where clause is numeric and stored as yyyymmdd
Any ideas?
Thx for your helpHow odd, I've tried it with a table and no error at all, could you please so
kind to give us DDL code?
regards,
current location: alicante (es)
"Billy" wrote:

> I originally posted this in the DTS forum, but then thought this particula
r
> issue is more "programming" than "DTS" oriented...so I am not multi postin
g.
> I've also changed the code slightly (found a couple of glaringly obvious
> errors!)
> I need to write a DTS package which is date oriented and I'm having proble
ms
> getting something coherent. I'm not a big guy in SQL Server, so please exc
use
> my ignorance.
> I need to get some sales information based on the previous day...however,
if
> the previous day was Monday, then I need to get the sales data for the
> Friday. The where clause looks like this (and it's not
> working...unsurprisingly).
> / ****************************************
*
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dd,getdate())) = 'MONDAY'
> then
> dateadd(dd,-3,getdate())
> --and ididat < 20060320
> else
> dateadd(dd,-1,getdate())
> --and ididat < 20060320
> end
> / ****************************************
*
> The error I get is
> Arithmetic overflow error converting expression to data type datetime.
> The IDIDAT field in the where clause is numeric and stored as yyyymmdd
> Any ideas?
> Thx for your help
>|||Check to make sure that the ididat column contains a valid date for every
row.
SELECT * FROM <table> WHERE IsDate(ididat) = 0
will show you any rows with invalid dates.
Tom
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:3A5E2E82-42E2-4464-97FF-D38B206CC460@.microsoft.com...
>I originally posted this in the DTS forum, but then thought this particular
> issue is more "programming" than "DTS" oriented...so I am not multi
> posting.
> I've also changed the code slightly (found a couple of glaringly obvious
> errors!)
> I need to write a DTS package which is date oriented and I'm having
> problems
> getting something coherent. I'm not a big guy in SQL Server, so please
> excuse
> my ignorance.
> I need to get some sales information based on the previous day...however,
> if
> the previous day was Monday, then I need to get the sales data for the
> Friday. The where clause looks like this (and it's not
> working...unsurprisingly).
> / ****************************************
*
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dd,getdate())) = 'MONDAY'
> then
> dateadd(dd,-3,getdate())
> --and ididat < 20060320
> else
> dateadd(dd,-1,getdate())
> --and ididat < 20060320
> end
> / ****************************************
*
> The error I get is
> Arithmetic overflow error converting expression to data type datetime.
> The IDIDAT field in the where clause is numeric and stored as yyyymmdd
> Any ideas?
> Thx for your help
>|||I got it to work (to a fashion - I at least got rid of the error) by using
this syntax around the DATEADD features
****************************************
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
****************************************
***********
However, see my lastest post regarding getting the date in a USEABLE format!
Thx
"Enric" wrote:
> How odd, I've tried it with a table and no error at all, could you please
so
> kind to give us DDL code?
> regards,
> current location: alicante (es)
>
> "Billy" wrote:
>|||Got it working like so
WHERE
IDPERI > 200601 and cast(ididat as int) =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
CONVERT(char(8), DATEADD(dd,-3,GETDATE()), 112)
--and ididat < 20060320
else
CONVERT(char(8), DATEADD(dd,-1,GETDATE()), 112)
--and ididat < 20060320
end
Thx for posts.
"Billy" wrote:

> I originally posted this in the DTS forum, but then thought this particula
r
> issue is more "programming" than "DTS" oriented...so I am not multi postin
g.
> I've also changed the code slightly (found a couple of glaringly obvious
> errors!)
> I need to write a DTS package which is date oriented and I'm having proble
ms
> getting something coherent. I'm not a big guy in SQL Server, so please exc
use
> my ignorance.
> I need to get some sales information based on the previous day...however,
if
> the previous day was Monday, then I need to get the sales data for the
> Friday. The where clause looks like this (and it's not
> working...unsurprisingly).
> / ****************************************
*
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dd,getdate())) = 'MONDAY'
> then
> dateadd(dd,-3,getdate())
> --and ididat < 20060320
> else
> dateadd(dd,-1,getdate())
> --and ididat < 20060320
> end
> / ****************************************
*
> The error I get is
> Arithmetic overflow error converting expression to data type datetime.
> The IDIDAT field in the where clause is numeric and stored as yyyymmdd
> Any ideas?
> Thx for your help
>sql

Date Logic for a DTS package

Hello,
I need to facilitate updating a data warehouse table with a DTS package that
updates an accounting table for premium amounts. I will do a one time run o
f
all the accounting records and after that would like to 'grab' just the
previous 2 months worth of data (on a nightly run, so that it is up to the
day) and add it to the existing data. Obviously, there will be overlap in
dates, so what would be a good way to handle this with my logic?
Thank you!Hi Patrice
It is not clear what exactly you are trying to achieve.
If you use a query as the source of your data, then you can limit the data
that is extracted by a criteria (assuming that you have datatime value that
will give you the last two months). If your destination is accessable throug
h
a linked server you could exclude those rows that do not exist in the
destination table (using the primary key), this will mean that the time
restriction is unneccessary. If you can't use a linked server, then you can
load the data into a staging table, and then selectively insert new records
(using the existance of the PK) from there.
John
"Patrice" wrote:

> Hello,
> I need to facilitate updating a data warehouse table with a DTS package th
at
> updates an accounting table for premium amounts. I will do a one time run
of
> all the accounting records and after that would like to 'grab' just the
> previous 2 months worth of data (on a nightly run, so that it is up to the
> day) and add it to the existing data. Obviously, there will be overlap in
> dates, so what would be a good way to handle this with my logic?
> Thank you!

Date function in DTS package

How can I make this work against an Access table through an ODBC dsn in my DTS package? I need to subtract 1 day from the current date.

Tried this, which is incomplete of course, (need to subtract the 1 day):

WHERE (TTDateTimeIn >= DATEDIFF(dd, 1, { fn CURDATE() }))

Got this error:
[Microsoft][ODBC Microsoft Access Driver] Too few Parameters. Expected 1.I believe the function you should be using is DATEADD, not DATEDIFF. And you will need to enclose that dd in single quotes.

Terri|||That's correct, I finally figured it out. What added to my troubles was needing to pull yesterdays data after midnight, but I got it.

(TTDateTimeIn >= DATEADD('y', - 1, { fn CURDATE() }))

It was painful building this DTS package using a DSN connection to get to locked MS Access tables.

Thanks.

Date format Conversion

First of all, I'm fairly new to SQL Server 2000, so please be patient and explicit.

I have a text file that I'm going to import using DTS on a scheduled interval. The text file has three different date fields that are all formatted as:

YYYYMMDD example: 20031004

I need to get this data formated as:

MM/DD/YYYY, example 10/04/2003

DTS does not do this. If I set the field type to DATETIME it gives errors and will not import the data.

HELP!

Thanks,
Troy D. YoungHi, Set up a dts transform data task.
the go to th etransformation tab and remove the transformations that

cover datetime fileds.
Make suer yoiu have highlighted both the source and destination fields.
Add a new transform of type date.
Modify the date time formates and hit preview.
thats should do it.

regards, brian|||Originally posted by contiguous1
Hi, Set up a dts transform data task.
the go to th etransformation tab and remove the transformations that

cover datetime fileds.
Make suer yoiu have highlighted both the source and destination fields.
Add a new transform of type date.
Modify the date time formates and hit preview.
thats should do it.

regards, brian

It took some time, but I figured it out. For some reason it only lets me do one datetime field at a time in a transformation. I had to create a transformation for each datetime field in the table.

Thanks,
Troy D. Young

Date field transfer problem!

Hello guys!

I have a problem transferring certain table columns
to excel with DTS. I have a colums where I have date
and time record like 02.10.03 14.45.33.
When I transfer this to excel, it loses the time from that
colums and has only 02.10.03.
What could be the problem?

Please help me,
JessicaYou should store the time in a separate column.|||Hi Jessica,

how is the exported field defined? TIMESTAMP?
And, which Task do you use for the export?

At this point I can imagine that the destination field in Excel has got a format like "DD.MM.YYYY" and not "DD.MM.YYYY hh.mm.ss", so that Excel just hides this information.

Perhaps I am wrong... It just should be a guess ;-)

Greetings,

Carsten

Date field in exported excel file

Date field in exported excel file.

I export a table to excel file by using DTS. It seems the date field show as ###### when I open the excel file. If I expend the column I see the date. Is there any way I export in away that this date field will not show up as #####.

Very Good Q just like if there is Nvarchar Data type with length 4000, why not it auto expend when i see it with select query in SQL? Continue....|||

Hi,

I didn't find any issue here. There is nothing wrong with your data; the cell simply isn’t big enough to display the result. Widen the column

SQL Server's task is to export data into excel & it does properly. You are not loosing any data here. When it export the data it doesn't format any data, it fills/writes all the data on Rows & Columns. It uses the default column width to fill the data(64 Pixels). You can expand your columns to read your data properly (there is no data loose)

SQL Server only export data & it never format your data (don't expect that it will bold your Header column, auto size your column width & etc.)

WHY IT IS NOT FORMATING?

Bcs SQL Server export taks uses the JET Provider to write the data. (here excel docuemnt will be treated as database rather than doc).It is not using EXCEL ActiveX EXE to fill the data.

The export code may use the following connection string to export,

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={File Path};Extended Properties="Excel 8.0;"

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as ####
#.This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect|||Where can I change the default Column With Dave?
"Dave Patrick" wrote:

> Another option is to change the default column width for the excel
> application Cells|Format|Cell Size|Default Width (FPN:this is user depende
nt
> setting)
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
[vbcol=seagreen]
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/e...0548151033.aspx
http://support.microsoft.com/kb/214123
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as ###### when I open the excel file. If I expend the column I see the date. Is there any way I export in away that this date field will not show up as #####.

I may be wrong, but I would think that this is a function of Excel itself -|||I agree with David, it should be a display issue of Excel. You can expand all folded columns: go to Format Menu in Excel->Column->click AutoFit Selection.

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as #####.
This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>
|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
|||Where can I change the default Column With Dave?
"Dave Patrick" wrote:

> Another option is to change the default column width for the excel
> application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
> setting)
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>
|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?
|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
[vbcol=seagreen]
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:
|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/excel/HA010548151033.aspx
http://support.microsoft.com/kb/214123
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as #####.This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:
> > Where can I change the default Column With Dave?|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/excel/HA010548151033.aspx
http://support.microsoft.com/kb/214123
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?

Date Convertion

I am currently running a DTS package to extract data from a DB2 database.
The code reads Select * from ABC where entrydate='12/15/2005'
This works fine but I need to automate the process by selecting the date
automatically. As soon as the entrydate = formula the extract do not work.
I have tried different versions of date formula
The format of the date field on the SQL table is smalldatetime and on DB2
it is date
Can some one helpVuka
Use 'yyyymmdd' format with SQL Server
Lookup CONVERT system function in the BOL
"Vuka" <Vuka@.discussions.microsoft.com> wrote in message
news:CF33E355-6FFA-4C1F-95A8-C9EABE0CDD29@.microsoft.com...
>I am currently running a DTS package to extract data from a DB2 database.
> The code reads Select * from ABC where entrydate='12/15/2005'
> This works fine but I need to automate the process by selecting the date
> automatically. As soon as the entrydate = formula the extract do not
> work.
> I have tried different versions of date formula
> The format of the date field on the SQL table is smalldatetime and on DB2
> it is date
> Can some one help

Date Conversion Problem

I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?

-----------

The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.

I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.

Thanks for your suggestions.|||Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?

Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?

No, I haven't used bcp before. I'll check it out.

In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:

Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function

The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.

No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file

I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.

I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0

I'll give it a try. Much thanks . . .|||Like:

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8))
GO

INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO

-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1

-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0

--Move to it's Final Destination

CREATE TABLE myTable00(Col1 datetime)
GO

INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1

SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Date Conversion - Flat File - YYYYMMDD

Hi,

What is the new way to transform flat file dates into SQL datetime datatype.Being average user in SQL 2000 DTS I would simply use "Date Time String Transformation Properties" and transform the date into the format I need, in SSIS I haven't found an elegant way of doing this.

My thoughts are to use “data conversion” utilizing substring expressions…

Thanks the help

Bill

Use a derived column transformation to substring the date field and then concatenate the parts together. Once that's done cast it to a datetime field.

Something like:

(DT_DBTIMESTAMP)(substring([yourDateField],5,2) + "/" + substring([yourDateField],7,2) + "/" + substring([yourDateField],1,4))

|||

bmilstead,

In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column

ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

bmilstead,

In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column

ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]

Thanks

Subhash Subramanyam

Right, but the format of the dates in the flat file are not DB_TIMESTAMP compatible. (YYYYMMDD)

Date Conversion

Hi,

Please help me on this conversion. I am using DTS to import data from text file to SQL Server 2000. I have these Date fields in the textfile

Date1 yyyymmdd
Date2 yyyymm

What corresponding data type should I define in SQL server. Datetime and smalldate does not work.

ThanksHowdy

The problem you have ( and I assume your data is text in a text file ) is that datetime expects a certain format for the data.

If you were importing date data in format '2003-09-30 14:00:00.000'
( including the single quotes ) all would work well. I use UK date format. If you are in the US its '2003-30-09 14:00:00.000'

Date format in BOL is not documented well, sadly.

So, may need to alter your text data as part of the DTS package ( not easy, and time consuming), or alternatively , import the data straight into a new table using DTS ( easier ), then modify it to insert the " - " etc to make it the correct format then copy it into another table if needed.

Let me know if I have interpreted your problem correctly.

Cheers,

SG.|||i would not alter the text file, rather, i would DTS it into a table where the datatype of the date fields is char(8) and char(6)

once you have the data loaded, you can then use SELECT INTO syntax to create your "final" table

e.g. if you've loaded yyyymmdd data into fieldx and yyyymm into fieldy, then you'd say

select
cast( left(fieldx,4)
+'-'+substring(fieldx,5,2)
+'-'+substring(fieldx,7,2) as datetime ) as fieldxdate
, cast( left(fieldy,4)
+'-'+substring(fieldy,5,2)
+'-01' as datetime ) as fieldydate
, ...
into newtable
from loadedtable

edit: cut & paste typo
rudy
http://r937.com/|||the temporary table idea works for sure, I have implemented that before. Plus you can use the Date Time String conversion in the Transformation tab. Where the source would be in yyyyMMdd format and the destination would be any of your desired formats.

Hope this helps.

Date (SQL 2000)

Hi,
I get this csv file to import and the date in the column of the CSV file
comes as 3/1/2007 0:00
When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
the month and the 3 is the date. Is there a way to fix this?
Thanks in advanceHello,
Open the CSV file and select the entire date column, right click, Format,
Date option and choose English -- United States and click ok.
This will change the date format to Unites states and after this you coulld
load into SQL Server table
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:A46A190A-A386-46F9-AA6A-8498001E21EF@.microsoft.com...
> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance|||Try using a datetime string transform on the column. After creating the
transform, set InputFormat property to : "M/d/yyyy H:mm"
--
Russel Loski, MCSD.Net
"stoney" wrote:

> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance

Date (SQL 2000)

Hi,
I get this csv file to import and the date in the column of the CSV file
comes as 3/1/2007 0:00
When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
the month and the 3 is the date. Is there a way to fix this?
Thanks in advance
Hello,
Open the CSV file and select the entire date column, right click, Format,
Date option and choose English -- United States and click ok.
This will change the date format to Unites states and after this you coulld
load into SQL Server table
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:A46A190A-A386-46F9-AA6A-8498001E21EF@.microsoft.com...
> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance
|||Try using a datetime string transform on the column. After creating the
transform, set InputFormat property to : "M/d/yyyy H:mm"
Russel Loski, MCSD.Net
"stoney" wrote:

> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance