How to deal with this DATETIME issue!

Question

Post reply

How to deal with this DATETIME issue!

TriggerMe

SSChampion

Points: 11752
More actions
June 7, 2005 at 4:48 am

#164737

I have a Stored Procedure that takes 2 DATETIME Input Paramaters. They will be parsed in as
@StartDate = '01/01/2005' @EndDate = '31/01/2005'
The column in the Database called LogTime is stored as 2005-01-20.
How do I CONVERT the 2 Input Paramaters to make it searchable on the LogTime column? At the moment I'm getting an error of 'out of range values' I know hwy, but I don't know how to fix it. Obviously, @StartDate will not match with 2005-01-20.
Hwo do I do this?
Kindest Regards,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Rafter SSC-Addicted Points: 472 More actions · Answer 1

Try using Set Dateformat at the start of your query/sproc

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 2

Hi SQL will handle the conversion for you. You can use either. between or <= and >=

HTH Mike

declare @StartDate datetime,

@EndDate datetime

set @StartDate = '01/02/2005'

set @EndDate = '01/31/2005'

Select * From temperatures

Where TempTime between @StartDate and @EndDate

select * from temperatures

Where temptime >=@StartDate and TempTime <= @enddate

TriggerMe SSChampion Points: 11752 More actions · Answer 3

SET DATEFORMAT does not work!

Michael Du Bois,

I have no choice but to parse the 2 input paramaters as dmy. Your example does not do this. Please read my initial thread.

Kindest Regards,

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 4

If you are parssing the 2 input paramaters as a string dmy there should be no problem. Or am I still missing something. Mike

Edited [if this does not work try an explicit cast to date time. Set Cast(@EndDate as DateTime]

declare @StartDate datetime,

@EndDate datetime

set @StartDate = '01/02/2005'

set @EndDate = '01/31/2005'

create table tests

(

dt datetime

)

INSERT INTO tests Values('2005-01-20')--

INSERT INTO tests Values('2005-01-21')--

INSERT INTO tests Values('2005-01-22')--

INSERT INTO tests Values('2005-01-23')--

INSERT INTO tests Values('2005-02-2')--

Select * From tests

Where dt between @StartDate and @EndDate

select * from tests

Where dt >=@StartDate and dt <= @enddate

drop table tests

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

DECLARE @StartDate CHAR(10)

DECLARE @EndDate CHAR(10)

SET @StartDate = '01/01/2005'

SET @EndDate = '31/01/2005'

SET @StartDate = RIGHT(@StartDate, 4) + '/' + SUBSTRING(@StartDate, 4, 3) + LEFT(@StartDate, 2)

SET @EndDate = RIGHT(@EndDate, 4) + '/' + SUBSTRING(@EndDate, 4, 3) + LEFT(@EndDate, 2)

CREATE TABLE #test

(LogTime VARCHAR(10))

INSERT INTO #test (LogTime) VALUES ('2005-01-20')

SELECT * FROM #test WHERE CAST(REPLACE(LogTime, '-', '/') AS DATETIME)

BETWEEN CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME)

DROP TABLE #test

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I might be missing something, but when you pass DATETIME parameters and compare with DATETIME values, there is no conversion needed at all. However, if LogTime is a varchar, CAST it to DATETIME and that's it. Using AJ's example:

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '20050101'

SET @EndDate = '20050131'

CREATE TABLE #test

(LogTime VARCHAR(10))

INSERT INTO #test (LogTime) VALUES ('2005-01-20')

SELECT *

FROM #test

WHERE CAST(LogTime AS DATETIME)

BETWEEN @StartDate AND @EndDate

DROP TABLE #test

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

The problem is if the regional settings of a client is dd/mm/yyyy and regional SQL server date format is mm/dd/yyyy. Client will send 02/03/2005 as 2nd March and Server will take it as 3rd february.

We have a work around. Take the Stored procedure parameter as varchar and warn the client to send the parameter as string in MM/dd/yyyy format. This will solve the problem.

or the other option is as Frank mentioned convert parameter and comparison in the query as '20030203'

Regards,
gova

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 8

Well, this was fun. This was kind of like taking a test. I take the data given provide a result and the only person that likes it is Frank (Of course he HAD to change it to be BETTER)....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Easwarapillai SSC Journeyman Points: 96 More actions · Answer 9

declare @StartDate varchar(10), @EndDate varchar(10)

set @StartDate = '01/02/2005'

set @EndDate = '01/31/2005'

Select * From temperatures

Where TempTime between @StartDate and @EndDate

select * from temperatures

Where temptime >= convert(smalldatetime,@StartDate,101) and TempTime <= convert(smalldatetime,@enddate,101)

Regards,
Easwar

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 10

As usual Frank has the answer. I did not read the original post close enough. Parsing the input to the desired format ISO standard will solve the problem.

Mike

TriggerMe SSChampion Points: 11752 More actions · Answer 11

Thanks to all for the input but this is how I have mananged to get it working.

DECLARE @StartDate VARCHAR(20), @EndDate VARCHAR(20)

SET DATEFORMAT DMY

SET @StartDate = '01/01/2005'

SET @EndDate '31/01/2005'

SELECT * FROM CallLog

WHERE CONVERT(DATETIME, LogDate, 103) BETWEEN @StartDate AND @EndDate

I don't understand why I had to go to this length but it works! I'm in Australia and the end user will only want to enter a date from the application as DMY.

If anyone has a simpler way, I would like to know.

Kindest Regards,