conversion of char data type to a datetime data type

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

SQL Server Development (2000)

conversion of char data type to a datetime data type

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-19 : 07:39:10

noms writes "can you guys help me and tell me what is wrong with my script here, it's throws the above error and to me it seems everything is right... it's too long but please look at it and tell me what you think:

declare @Type char(2)

SET NOCOUNT ON

DECLARE @flag bit
DECLARE @WEEK_START_DATE smalldatetime, @WEEK_END_DATE datetime
DECLARE @beginDate smalldatetime, @endDate smalldatetime

set @type = 'hv'
set @beginDate = '2006/06/12'
set @endDate = getdate()
SELECT @flag = 1

SELECT @flag = 1
create table #temp
(StartDate datetime, EndDate datetime, GPRS smallint, FailureRate int, NoBarCode smallint, View_ smallint,
Security int, ExternalNoise smallint,
Topography int, Quality int, Condition int, ExteriorWalls varchar(20), [#Bedrooms] varchar(20),
[#LivingRooms] varchar(20), Min_Form_Count int, min_Date datetime, max_form_count int, max_Date datetime)

create table #temp20
(StartDate datetime, EndDate datetime, [Total Entries Received] int, GPRS_timestamp datetime)

WHILE @flag = 1
BEGIN

--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @beginDATE

--GET THE WEEK END DATE
SELECT @WEEK_END_DATE= @beginDATE + 6

IF @WEEK_END_DATE < @enddate and (@enddate- @WEEK_END_DATE <7)
BEGIN
SELECT @WEEK_END_DATE = @enddate
SELECT @flag = 0
END

IF @WEEK_START_DATE >@endDate or @WEEK_END_DATE > @enddate
BREAK;
ELSE
BEGIN TRANSACTION
INSERT INTO #temp20
SELECT @WEEK_START_DATE as StartDate,@WEEK_END_DATE as EndDate, COUNT(1) AS [Total Entries Received],
convert(varchar(10),GPRS_timestamp,103) as GPRS_timestamp
FROM DC_Forms INNER JOIN
DC_Batches ON DC_Forms.PrintBatchID = DC_Batches.PrintBatchID INNER JOIN
DC_Hierarchy ON DC_Batches.DCUnitID = DC_Hierarchy.ID
WHERE DC_Hierarchy.Type = @Type AND
GPRS_timestamp between @WEEK_START_DATE and @WEEK_END_DATE
and GPRS_timestamp IS NOT NULL AND FormDataID IS NOT NULL
group by convert(varchar(10),GPRS_timestamp,103)

COMMIT TRANSACTION

begin transaction
INSERT INTO #temp
SELECT TOP 1 @WEEK_START_DATE as StartDate, @WEEK_END_DATE as EndDate,
(SELECT COUNT(1)
FROM DC_Forms INNER JOIN
DC_Batches ON DC_Forms.PrintBatchID = DC_Batches.PrintBatchID INNER JOIN
DC_Hierarchy ON DC_Batches.DCUnitID = DC_Hierarchy.ID
WHERE DC_Batches.ProcessID = 7 and dc_hierarchy.type = @Type and dc_forms.Stray = 0 AND dc_forms.GPRS_Timestamp BETWEEN @WEEK_START_DATE
and @WEEK_END_DATE + 1) AS GPRS,
((SELECT COUNT(1)
FROM DC_Forms INNER JOIN
DC_Batches ON DC_Forms.PrintBatchID = DC_Batches.PrintBatchID INNER JOIN
DC_Hierarchy ON DC_Batches.DCUnitID = DC_Hierarchy.ID
WHERE DC_Hierarchy.Type = @Type and
ProcessedOn between @WEEK_START_DATE and @WEEK_END_DATE + 1) /
((SELECT case (COUNT(1)) when 0 then 1 else COUNT(1) end
FROM DC_Forms INNER JOIN
DC_Batches ON DC_Forms.PrintBatchID = DC_Batches.PrintBatchID INNER JOIN
DC_Hierarchy ON DC_Batches.DCUnitID = DC_Hierarchy.ID
WHERE DC_Batches.ProcessID = 7 and dc_hierarchy.type = @Type and dc_forms.Edit_Result >= 2 AND dc_forms.stray = 0
AND dc_forms.Edit_End BETWEEN @WEEK_START_DATE and @WEEK_END_DATE + 1)) *100) AS [Failure Rate],
(SELECT COUNT(1)
FROM dcf_Validation
WHERE PostedOn BETWEEN @WEEK_START_DATE and @WEEK_END_DATE + 1 and pin = 0 OR
pin IS NULL) AS NoBarCode,
(SELECT COUNT(1)
FROM DC_Forms INNER JOIN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 07:54:50

Because you are storing some dates as VARCHAR, instead of DATETIME, there is no validation for the records. This means you have some "dates" that SQL Server does not know how to convert to a real DATETIME.
As for example 45/13/2005.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-19 : 08:20:00

"set @beginDate = '2006/06/12'"

Always use 'yyyymmdd' format for string constant dates - i.e.

set @beginDate = '20060612'

even though you have the three elements in the right order it is not guaranteed to be treated as yyyy/mm/dd unless you go to the extra trouble of using SET DATEFORMAT or using a CONVERT on the string.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources