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 formating - Military

Hello all,

I am trying to import an excel spreadsheet that has a specific date column. The column is saved as military time. For example, one of the records is:

04-12-2007 09:30

But, when i import the data into sql 2005, the above date is displayed as:

2007-04-12 09:30

I need it to display like the same way that it's being displayed in the excel spreadsheet.

My field in sql is nvarchar(100) and it still doesn't work.

Any ideas?
Thanks in advance.

Hi Friend,

You can play with the data at UI Level while binding the data you can change the Date Format as you wish, by the way are u using GridView to show the records ??

|||Hi You can also refer to http://support.microsoft.com/kb/173907Thanks...Akhil|||

Nvarchar for dates is not recommended. You will not be able to sort by date or perform any date calculations, because your value will be treated as text. The recommended format for dates in Sql Server is YYYY-MM-DD hh:mm:ss. Once you extract a date/time value for display, you can fomat it in any way you want using the String.Format() method. Here are the expressions used to generate custom formats:http://blog.stevex.net/index.php/string-formatting-in-csharp/. The page is for C#, but the expressions are equally valid for VB.

Date format export/import

Help!

I have a table that has datetime format field, I exported the table to
a csv while I dropped it and tried some other data, but now sql
doesn't recognise the date format for importing, heck I don't!

The dates look something like:

40:58.1

Whick means nothing to me, or any of us here for that matter...

Any ideas?

JohnHi

Without knowing how you exported the file it is hard to say how you came
to have the dates you have. It seems that you have something like
minutes, seconds and the first digit of the milliseconds.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like “0001-01-01”. Before I import directly my AS400 tables into Access without problems because Access read those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value “0001-01-01” to “1901-01-01” and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like “0001-01-01”. I could not do modification to the AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
Montreal
JP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison
|||Thanks,
but not all the record have this value. When I import with package, I could go around it. But I need to be live, so, from my SQL Server, I link into the AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it said “conversion failure
.
Regards,
Jean-Paul
|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

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 conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like â'0001-01-01â'. Before I import directly my AS400 tables into Access without problems because Access read those date as â'1901-01-01â'. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value â'0001-01-01â' to â'1901-01-01â' and then alter the data type back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like â'0001-01-01â'. I could not do modification to the AS400 source. Is there a way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with
a package to a text file, there are dates like “0001-01-01”. Before I i
mport directly my AS400 tables into Access without problems because Access r
ead those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Pa
ckage it crash unless I first change the fields in my SQL table to varChar(1
5), do the importation, change the value “0001-01-01” to “1901-01-01
and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields.
I try to link the AS400 source to my SQL Server, I still could not read tab
les that have dates like “0001-01-01”. I could not do modification to th
e AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||Thanks,
but not all the record have this value. When I import with package, I could
go around it. But I need to be live, so, from my SQL Server, I link into the
AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it
said “conversion failure
.
Regards,
Jean-Paul|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date / Time types

Hi, I'be just started to import data into sqlserver from ms access, and it automatically stores my dates as smallint length 4. Is this ok? For display purposes I want dd/mm/yyyy. Will 4 bytes store this?I generally recommend DATETIME unless I know that the values can be stored properly in a SMALLDATETIME column. See the Using Date and Time data (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7uzy.asp) discussion in MSDN for more details.

-PatP

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