DateTime Problems - 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

Transact-SQL (2000)

DateTime Problems

Author

Topic

fatboymal
Starting Member

10 Posts

Posted - 2006-02-03 : 10:21:30

Hi, I have been looking in on this site for some time, but this is the first time I have had a problem that searching other posts cannot answer, any help would be greatly appreciated.

I am running a query in a stored procedure that counts the records made during the current day, yesterday, and the prior week. Yesterday and the prior week work just fine, but today fails!

The code is:

-----------------------------------------------------

CREATE PROCEDURE sp_get_stats(
@S1T INT OUTPUT,
@S1Y INT OUTPUT,
@S17 INT OUTPUT,
)
AS

DECLARE @startDay DATETIME

SET @startDay = DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

SELECT @S1T = COUNT(*)

FROM tbl_transaction

WHERE siteID='0' AND refundStatus='0' AND tranDateTime > @startDay AND tranDateTime < GETDATE()

SELECT @S1Y = COUNT(*)

FROM tbl_transaction

WHERE siteID='0' AND refundStatus='0' AND tranDateTime > DATEADD(day,-1,@startDay) AND tranDateTime < @startDay

SELECT @S17 = COUNT(*)

FROM tbl_transaction

WHERE siteID='0' AND refundStatus='0' AND tranDateTime > DATEADD(day,-7,@startDay) AND tranDateTime < @startDay

GO

-------------------------------------------------

Column trandDateTime in table tbl_transaction is defined as DateTime stamp.

The procedure provides @S17 AND @S1Y with the numbers I expect, but @S1T (which I wanted to be counting todays records) gives a massively higher number than expected.

Using OSQL I manually ran a query as follows:

select * from tbl_transaction where siteID='0' and tranDateTime > '2006-03-02'

and it returns all the rows with with todays date, AND all those from Jan 4th to Jan 14th??

Very confused, please help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-03 : 10:34:57

Something sounds fishy. Can you script out the structure of the table and post it along with several insert statements for rows that are being incorrectly returned? (Jan 4 - Jan 14)

EDIT:
why did you use osql to test the query? do you still get bad results straight from query analyzer?

Be One with the Optimizer
TG

fatboymal
Starting Member

10 Posts

Posted - 2006-02-03 : 10:51:07

TG,

I use OSQL as I only have MSDE on the server on which the populated database is installed.

The table script is:

-----------------------------------------

CREATE TABLE [dbo].[tbl_transaction] (
[tranRef] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tranTotal] [money] NULL ,
[tranCurr] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[referName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tranID] [int] NULL ,
[tranDateTime] [datetime] NULL ,
[siteID] [int] NULL ,
[refundStatus] [int] NULL ,
[emailAddress] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[geoArea] [int] NULL
) ON [PRIMARY]
GO
--------------------------------------------

The rows are populated using the following stored procedure:

--------------------------------------------

CREATE PROCEDURE sp_transaction(
@tranref varchar(30),
@IPAddress varchar(20),
@trantotal money,
@tranCurr varchar(5),
@referName varchar(20),
@tranID int,
@tranDateTime datetime,
@siteID int,
@refundStatus int,
@emailAddress varchar(50),
@geoArea int
)
AS

INSERT INTO tbl_transaction(
tranref,
IPAddress,
tranTotal,
tranCurr,
referName,
tranID,
tranDateTime,
siteID,
refundStatus,
emailAddress,
geoArea
)
VALUES(
@tranref,
@IPAddress,
@trantotal,
@tranCurr,
@referName,
@tranID,
@tranDateTime,
@siteID,
@refundStatus,
@emailAddress,
@geoArea
)
GO
----------------------------------------------

I am not quite sure about the insert statement you mention, but the following are entries returned for tranDateTime from a couple of the "fishy" results

----------------------------------------------

U19200675826AM 202.72.171.126 6.9500 EU Unknown 0 2006-09-01 07:58:26.000 0 0 zzz@bigpond.com.au 3

UL1122006112551PM 88.106.24.125 4.9900 GB Google 0 2006-12-01 23:25:51.000 0 0 zzz@tiscali.co.uk 1

Cheers.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-03 : 11:03:38

Looks like those dates are not Jan 9 and Jan 12 but rather Sept 1 and Dec 1. Are you entering yyyy-dd-mm format instead of yyyy-mm-dd when you call that insert procedure?

Be One with the Optimizer
TG

fatboymal
Starting Member

10 Posts

Posted - 2006-02-06 : 09:43:50

TG,

Many thanks for the comments. The database is populated with ASP, with VBScript providing the date formatted using the vbGeneraldate function. Checking the regional settings on the server, the date is set to ddmmyyyy.

I assume you are correct in that if I run the following query using osql:

Select * from tbl_transaction where tranDateTime > GETDATE()

then I get a series of rows with dates of form 2006-03-01 to 2006-12-01 etc (These were entered as, and intended to be, dates in January, not 1st Mar and 1st Dec)

Also, if I just run:

Select GETDATE()

Then it returns:

2006-02-06

I've now really confused myself on how to resolve this difference. Do I need to reformat all existing entries, or just the query? Any further assistance would be really appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 09:58:34

>>Select * from tbl_transaction where tranDateTime > GETDATE()

That should be

Select * from tbl_transaction
where tranDateTime >= DateAdd(day,Datediff(day,0,GETDATE()),0)

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-06 : 10:06:47

Ughh. Your query (at the beginning of the thread) is correct.
The biggest problem is that you have incorrect values in your TranDateTime column. And perhaps other date columns that were populated the same way. This could be a tricky thing to fix if you also have values added correctly because you won't necessarily know which are correct and which are not correct. You will need to UPDATE the datetime values that are incorrect. However you will also need to coordinate a fix to your vb code that populates these values. If you fix the problem with the vb code before you correct the dates then you will have a mixture of good and bad dates. By any chance is this development data that can be wiped out and start over again?

Be One with the Optimizer
TG

fatboymal
Starting Member

10 Posts

Posted - 2006-02-06 : 10:07:35

quote:
Originally posted by madhivanan

>>Select * from tbl_transaction where tranDateTime > GETDATE()

That should be

Select * from tbl_transaction
where tranDateTime >= DateAdd(day,Datediff(day,0,GETDATE()),0)

Thanks, but this produces exactly the same (wrong) result. The problem is that when I use GETDATE() SQL gives me a date of form YYYY-MM-DD, whereas I have inserted all the dates in the form YYYY-DD-MM.

Can I reformat the tranDateTime column without losing the data, or can I just change the query in some way?

Confused....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-06 : 10:51:35

Here is some samle code to show how to change a datetime to flip the day and month. As I said (above) you should make sure you are only changing the dates that you know are wrong. Also you should coordinate that change with the application code fix.


set nocount on
create table #temp (rowid int identity(1,1), dt datetime, newDt datetime null)

--insert values as yyyy-dd-mm
insert #temp (dt)
select '2006-1-1' union all
select '2006-2-1' union all
select '2006-3-1' union all
select '2006-4-1' union all
select '2006-5-1' 

select rowid, dt, newDt from #temp

--update to yyyy-mm-dd by flipping the month and day
update t set
	newDt = convert(datetime, 
	datename(year,dt) + '-' 
	+ datename(day,dt) + '-' 
	+ convert(varchar,datepart(month,dt)) + ' '
	+ datename(hour, dt) + ':'
	+ datename(minute, dt) + ':' 
	+ datename(second, dt) + '.'
	+ datename(millisecond, dt)
	)
from	#temp t
where	rowid < 7

select rowid, dt, newDt from #temp

drop table #temp

Be One with the Optimizer
TG

fatboymal
Starting Member

10 Posts

Posted - 2006-02-06 : 13:02:12

Cheers TG, it is not all clear at present, but you have certainly given me enough to work out (create temp table with old datetime values, convert them by swapping day and month, then update original table - I think

).

Thankfully there is a second column in the original table that will allow me to check if the tranDateTime is wrong or not.

Many thanks for your assistance.

fatboymal
Starting Member

10 Posts

Posted - 2006-02-07 : 07:57:15

TG, could you help one more time?

I created the copy table (tbl_temp) with the old (wrong) date and time (oldtranDateTime), and identified which ones were wrong (interestingly this was all from the 1st or 2nd of Jan 2006 - though the code did not change!), which is rowID>74285

I then tried the following:

----------------------------------
update tbl_temp
set newtranDateTime = convert(datetime,datename(year,oldtrandatetime)+'-'
+datename(day,oldtrandatetime)+'-'
+convert(varchar,datepart(month,oldtrandatetime))+' '
+datename(hour,oldtrandatetime)+':'
+datename(minute,oldtrandatetime)+':'
+datename(second,oldtrandatetime)+'.'
+datename(millisecond,oldtrandatetime))
where rowID > 74285
go
---------------------------------------
It fails with "Arithmetic error converting expression to datatype datetime"

I think I must be doing something fundamentally wrong in the update statement, as it works if I do the same for a row at a time!

Many thanks again..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-07 : 10:16:43

That error means you have incorrectly identified a good date as being bad. If you try to update '2006-12-31' to '2006-31-12' you will receive that error. (because there is no month 31)

Be One with the Optimizer
TG

fatboymal
Starting Member

10 Posts

Posted - 2006-02-07 : 11:22:39

Thanks TG.

Turns out the problem arose on Jan 1 to Jan 12 2006, and from Feb 1 to date.

SQL must correct the ordering when presented with 2006-13-01?

Cheers again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-07 : 11:43:36

>>SQL must correct the ordering when presented with 2006-13-01?
I don't think it will do that. You can test it with the following statement.


select convert(datetime, '2006-13-01')

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If all your data was entered using the same code I don't understand how you could have some good dates and some bad dates.

Be One with the Optimizer
TG

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-07 : 12:22:19

The only way I can think this will be inserted in a different format is that one of your machines has the wrong regional setting, this would account for the dates being inserted differently, but if it was an invalid date at the server level, an error should have been thrown up at the time?!?

fatboymal
Starting Member

10 Posts

Posted - 2006-02-08 : 06:49:36

Thanks for the comments both. I have correct the error, but only by creating a date time string in the form the SQL likes it using code, and passing it as a adVarWChar to the stored procedure, where it is accepted as a DateTime.

I was using ASP and FormatDateTime(Now(), vbGeneralDate) to creat the date, and passing it as a adDBTimeStamp to the stored procedure, where it is accepted as a DateTime.

This was creating a date of form m/d/yyyy or mm/dd/yyyy no matter what the regional date setting was on the server. The book I have states that vbGeneralDate SHOULD use the regional settings (which are set to yyyy-mm-dd), but doing further research on the net, some claim that it pays no attentions to the settings, and always gives m/d/yyyy

Passing 2/8/2006 gave SQL creating a date of 2006-08-02
Passing 1/31/2006 gave SQL creating a date of 2006-01-31

Strange.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 06:56:59

Instead of FormatDateTime(Now(), vbGeneralDate) cant you simply use now()?

Madhivanan

Failing to plan is Planning to fail

fatboymal
Starting Member

10 Posts

Posted - 2006-02-08 : 07:40:13

quote:
Originally posted by madhivanan

Instead of FormatDateTime(Now(), vbGeneralDate) cant you simply use now()?

For a second there I thought this would be a really simple solution, but on testing it produce the same result, ie m/d/yyyy

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-08 : 07:48:41

>>For a second there I thought this would be a really simple solution, but on testing it produce the same result, ie m/d/yyyy

mm/dd/yyyy is a valid date format. Your original problem was due to yyyy-dd-mm. Besides, by using just "now()", doesn't that result in an actual datetime value rather than a formatted string? That is what you should work towards since the SP correctly uses datetime for the parameter datatype.

select convert(datetime, '2/8/2006') results in feb 8 rather than aug 2.

Be One with the Optimizer
TG

fatboymal
Starting Member

10 Posts

Posted - 2006-02-08 : 08:26:44

Thanks TG, I have finally reformatted all the incorrect dates and changed the code.

Even though the old code failed on passing 2/8/2006 (gave SQL creating a date of 2006-08-02) if I manually go

select convert(datetime, '2/8/2006')

it would correctly give 2006-02-08 as you stated, so the error must have been in passing the datetime to the procedure somewhere, I assume. I have now just circumvented the whole problem by building the string in the correct format.

Cheers for all your help, much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources