Conversion Problem - 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

General SQL Server Forums

New to SQL Server Programming

Conversion Problem

Author

Topic

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-14 : 17:25:03

I am working with a POS database and trying to drop the seconds using cast(convert(varchar, dateofsale, 105) as datetime) and sql server returns an error message:

"Server: Msg 242, Level 16, State 3, Line 14
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

The POS software uses a datetime stamp for recording register data and I would like to drop the seconds so that I can compare dates without the seconds. Im not really sure what is happening here, if maybe the data is somehow corrupt or something. I cannot really tell. But I have used this conversion as part of other queries but for some reason I am now getting an error. So, I guess what I am saying is that it works but for some reason not with every transaction.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:26:35

It would help A LOT if you also could post some sample data, so we can see what you are working with...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:27:54

Try this

SELECT * FROM {YourTableNameHere} WHERE ISDATE(DateOfSale) = 0

to get all records not convertable to a datetime datatype!

Peter Larsson
Helsingborg, Sweden

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-14 : 17:32:11

Okay, I tried that and it returned zero records.

Here is a little more information:

Declare @comparetabletwo table (cclientuid nvarchar(50), cdateofsale datetime)
Declare @countervar nvarchar(50)
set @countervar = null
select top 1 @countervar = cclientuid from @comparetable order by cclientuid
while @countervar is not null
begin
insert into @comparetabletwo(cclientuid, cdateofsale)
select top 1 clientuid, cast(convert(varchar, dateofsale, 105) as datetime)
from stregister.dbo.reg_transactions where clientuid = @countervar order by dateofsale
select top 1 @countervar = cclientuid from @comparetable where cclientuid > @countervar order by cclientuid
if @@rowcount = 0 begin set @countervar = null end
end

I hope this helps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:37:05

Replace this part
cast(convert(varchar, dateofsale, 105) as datetime)

with
dateadd(day, datediff(day, 0, dateofsale), 0)

Peter Larsson
Helsingborg, Sweden

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-14 : 17:46:52

Okay, you rock. That worked out. Just a couple of more questions. So basically, this: dateadd(day, datediff(day, 0, dateofsale), 0) takes the second data and makes it zero by using datediff 0? Also, how can I compare the difference between two dates from two columns?
Lastly, I noticed that there were conversion errors when I was using cast(convert(varchar, dateofsale, 105) as datetime) where the month and day were switched. So that would probably be because of the type I was converting from which it '2007-03-15' format? Thanks in advance for all of your help, both previous and post this post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 18:03:32

You can keep your old code, if you put either

SET DATEFORMAT dmy

or

SET DATEFORMAT mdy

first in code block.

Peter Larsson
Helsingborg, Sweden

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-14 : 18:07:16

Fantastic, you have been such a big help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources