Date Format Problems

Date Format Problems

Post by Steven Patan » Sat, 17 Jul 1999 04:00:00



 Why is it when I insert a date value using DTS eg.(12/26/1998) in the
format
 MM/DD/YYYY Yet when i query the month using a datepart function in sql i
get
 a 9 or the Day column instead of the month value?

 --
 Steven Patane
 Administrator
 BEYOND NET NQ

 
 
 

Date Format Problems

Post by Roy Harv » Sat, 17 Jul 1999 04:00:00


Steven,

Quote:> Why is it when I insert a date value using DTS eg.(12/26/1998) in the
>format
> MM/DD/YYYY

So the data is in SQL Server when the rest of your processing happens?
What is the column's data type?

Quote:>Yet when i query the month using a datepart function in sql i
>get
> a 9 or the Day column instead of the month value?

Can you show us the output of something like:

  SELECT datecol, datepart(day,datecol)
    FROM whatever

Roy

 
 
 

Date Format Problems

Post by Steven Patan » Mon, 19 Jul 1999 04:00:00


Roy,

Yes I use DTS to import dates, and then I use a SQL query through asp to
generate my reports.The Datecol column has a Datatype of datetime

This is what I get when I execute the following:

SELECT [DATECol], datepart(day,datecol) FROM tbl_usagelog

DATECol
---------------------------                 -----------
1998-10-12 00:00:00.000     12
1998-10-12 00:00:00.000     12
1998-10-12 00:00:00.000     12
1998-10-12 00:00:00.000     12
1998-10-12 00:00:00.000     12
1998-12-12 00:00:00.000     12
1998-12-12 00:00:00.000     12
1998-12-13 00:00:00.000     13 << You can see here that the month and day
colum has swapped around all this data should be for the month of December
98 not the 12th of October.
1998-12-13 00:00:00.000     13
1998-12-13 00:00:00.000     13

--
 Steven Patane
 Administrator
 BEYOND NET NQ


Quote:> Steven,

> > Why is it when I insert a date value using DTS eg.(12/26/1998) in the
> >format
> > MM/DD/YYYY

> So the data is in SQL Server when the rest of your processing happens?
> What is the column's data type?

> >Yet when i query the month using a datepart function in sql i
> >get
> > a 9 or the Day column instead of the month value?

> Can you show us the output of something like:

>   SELECT datecol, datepart(day,datecol)
>     FROM whatever

> Roy

 
 
 

Date Format Problems

Post by Steven Patan » Mon, 19 Jul 1999 04:00:00


I've had a closer look at the data in my table that dts has modifyed. The
orginal data looks like this.

FORMAT MM/DD/YYYY

"12/01/1998","01:48:20","JOHNNY"
"12/01/1998","01:51:03","SID6.7"
"12/01/1998","01:52:22","JOHNNY"
"12/01/1998","01:52:47","SID6.7"
"12/01/1998","01:53:15","JOHNNY"
"12/01/1998","01:53:25","MNEMONIC"

Yet DTS Changed the date to

FORMAT DD/MM/YYYY
12/01/1998
12/01/1998
12/01/1998

Any Ideas, how to get my server to either reorder the dates?

 
 
 

Date Format Problems

Post by In Ch'ang Ki » Tue, 20 Jul 1999 04:00:00


Steve..

I don't understood your focus.

Do explains about your problem again?

Steven Patane (?) ?T??????- ??o?????...

>Roy,

>Yes I use DTS to import dates, and then I use a SQL query through asp to
>generate my reports.The Datecol column has a Datatype of datetime

>This is what I get when I execute the following:

>SELECT [DATECol], datepart(day,datecol) FROM tbl_usagelog

>DATECol
>---------------------------                 -----------
>1998-10-12 00:00:00.000     12
>1998-10-12 00:00:00.000     12
>1998-10-12 00:00:00.000     12
>1998-10-12 00:00:00.000     12
>1998-10-12 00:00:00.000     12
>1998-12-12 00:00:00.000     12
>1998-12-12 00:00:00.000     12
>1998-12-13 00:00:00.000     13 << You can see here that the month and day
>colum has swapped around all this data should be for the month of December
>98 not the 12th of October.
>1998-12-13 00:00:00.000     13
>1998-12-13 00:00:00.000     13

>--
> Steven Patane
> Administrator
> BEYOND NET NQ



>> Steven,

>> > Why is it when I insert a date value using DTS eg.(12/26/1998) in the
>> >format
>> > MM/DD/YYYY

>> So the data is in SQL Server when the rest of your processing happens?
>> What is the column's data type?

>> >Yet when i query the month using a datepart function in sql i
>> >get
>> > a 9 or the Day column instead of the month value?

>> Can you show us the output of something like:

>>   SELECT datecol, datepart(day,datecol)
>>     FROM whatever

>> Roy

 
 
 

Date Format Problems

Post by Roy Harv » Tue, 20 Jul 1999 04:00:00


Steven,

I would assume that the data was loaded using a different language
that defaults to reading dates as DD/MM/YYYY.  I've never had to deal
with the language issue myself, but I would take a close look at what
account the DTS process used when loading the data.

Roy


>I've had a closer look at the data in my table that dts has modifyed. The
>orginal data looks like this.

>FORMAT MM/DD/YYYY

>"12/01/1998","01:48:20","JOHNNY"
>"12/01/1998","01:51:03","SID6.7"
>"12/01/1998","01:52:22","JOHNNY"
>"12/01/1998","01:52:47","SID6.7"
>"12/01/1998","01:53:15","JOHNNY"
>"12/01/1998","01:53:25","MNEMONIC"

>Yet DTS Changed the date to

>FORMAT DD/MM/YYYY
>12/01/1998
>12/01/1998
>12/01/1998

>Any Ideas, how to get my server to either reorder the dates?

 
 
 

1. Date Format Problem/Internal Date Storage

Hi all,

I have just experienced a problem with invalid inserted dates in Oracle.

Using the dump statement on the date field in my table, I found that the year
byte is 201, whereas I was expecting this to be 101.

I am using a Prepared Statement using JDBC.  My conclusion is that the problem
is to do with the version of classes111.zip (Oracle JDBC driver classes) which
uses the OCI73JDBC.DLL.  

The only problem is that I cannot find any documentation of whether this is a
bug to do with the above.

I have validated the date that I am passing, but what concerns me most is the
fact that the year byte should never be greater than 199, whereas I'm getting
201.

Any knowledge about the problem.

TIA,

Stephen

2. OpenRoad, NT, and INTEROP DLL

3. Date Format problem

4. DSN TEXT FILE

5. Date format problem with BDE

6. New-Era sites in USA?

7. Date format problem with servlet/Standalone application

8. Pdox 4.0 & Win 95 locks

9. Date format problem

10. Date Format Problem

11. Date Format Problem?

12. Date format problem?

13. International Date format problem using System account