if you post your exact select/insert statement with the format you are using
it would be possible to give correct solution. By the way following are the
tips that you can use.

You can SET DATEFORMAT and modify code as follows.

create table test(dt datetime)
go
declare @dt varchar(32)
set @dt ='26/07/2002'
set dateformat dmy --change dateformat
insert into test values(convert(datetime,@dt)) ;
select * from test;
go

OR other method would be to use explicit coversion of the
date using CONVERT function.

create table test(dt datetime)
go
declare @dt varchar(32)
set @dt ='26/07/2002'
insert into test values(convert(datetime,@dt, 103)) ; --103 stands for the
format dd/mm/yyyy
select * from test;
go

--
-Vishal

"Carmen" <cjohnston (AT) pbsj (DOT) com> wrote

Here's the exact script I'm using.

This is just to calculate the date. The calculated date
is then being used in another query to gather information.

Thanks again!
CJ

-- Date Calc Script

declare
@Today datetime,
@CurrentMonth int,
@CurrentYear int,
@PrevMonth int,
@StartDate varchar(40),
@EndDate varchar(40)

set @Today = getdate()
set @CurrentMonth = DATEPART(month, @Today)
set @CurrentYear = DATEPART(year, @Today)
set @PrevMonth = DATEPART(month, @Today) -1

select @CurrentMonth as 'CurrentMonth', @CurrentYear
as 'CurrentYear', @PrevMonth as 'PrevMonth'
select 'Today is :' + convert(char(20), @Today)

if (DATEPART(day, @Today) between 1 and 15)
begin
set @StartDate = '''' + rtrim((convert(char(4),
@CurrentYear))) + '-16-0' + rtrim((convert(char(3),
@PrevMonth))) + ' 00:00:00:000'''
set @EndDate = '''' + rtrim((convert(char(4),
@CurrentYear))) + '-01-0' + rtrim((convert(char(3),
@CurrentMonth))) + ' 00:00:00:000'''
select 'Between 1 and 15'
select cast(@StartDate as datetime), cast(@EndDate
as datetime)
select @StartDate + @EndDate
end
else
begin
set @StartDate = '''' + rtrim((convert(char(4),
@CurrentYear))) + '-01-0' + rtrim((convert(char(3),
@CurrentMonth))) + ' 00:00:00:000'''
set @EndDate = '''' + rtrim((convert(char(4),
@CurrentYear))) + '-16-0' + rtrim((convert(char(3),
@CurrentMonth))) + ' 00:00:00:000'''
select 'Between 16 and 31'
select @StartDate + @EndDate
select cast(@StartDate as datetime), cast(@EndDate
as datetime)
select convert(datetime, @StartDate, 101)
end


-- Second query that gathers information based on
calculated date:
....
where EecEEID = EepEEID
and EecLocation = LocCode
and EecEmplStatus <> 'T'
and EecDateOfLastHire >= @StartDate
and EecDateOfLastHire <= @EndDate

you are putting 2 additional double quotes unnecessarily
change your code as follows.

set @StartDate = rtrim((convert(char(4), @CurrentYear))) + '-01-0' +
rtrim((convert(char(3), @CurrentMonth))) + ' 00:00:00:000'
set @EndDate = rtrim((convert(char(4), @CurrentYear))) + '-16-0' +
rtrim((convert(char(3), @CurrentMonth))) + ' 00:00:00:000'

--
-Vishal

"Carmen" <cjohnston (AT) pbsj (DOT) com> wrote

Why are you converting the datetime values to Varchars?

Alternative method of calculating your dates:
declare
@Today datetime,
@StartDate smalldatetime,
@EndDate smalldatetime

--set @Today = getdate()
set @Today = LEFT ( CONVERT(datetime, getdate(),
103), 11 ) --This sets the time to '00:00:00'

--set @Today = '2003-07-12'
--set @Today = '2003-07-15'
--set @Today = '2003-12-12'
--set @Today = '2003-12-18'
--set @Today = '2004-01-12'
--set @Today = '2004-01-18'

if (DATEPART(day, @Today) between 1 and 15)
Begin
set @StartDate = DATEADD(month, -1, (DATEADD(day, (16 -
(DATEPART(day, @Today))), @Today)) )
set @EndDate = DATEADD(day, (1 - (DATEPART(day,
@Today))), @Today)
End
Else
Begin
set @StartDate = DATEADD(day, (1 - (DATEPART(day,
@Today))), @Today)
set @EndDate = DATEADD(day, (16 - (DATEPART(day,
@Today))), @Today)
End

select @StartDate as 'Start',
@EndDate as 'End'

Cheers,
Eric