Help filtering for dates in a particular format

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)

Help filtering for dates in a particular format

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-17 : 23:54:35

Hello All,

I wrote an openquery that works, but does not insert any data into the Testdata table. The problem is that the variable is storing the date in this format mm/dd/yyyy but need to store it in this format mm/dd/yyyy 00:00:00. Does anyone know how to query for a date that is shown below? Please advice. Thanks.

declare @dtRptDate  as DATETIME
set @dtRptDate = '09/07/2007'
--** DECLARE VARIABLE **--
DECLARE @sqlstring  nvarchar(1000)


--** QUERY AND LOAD DATA **--
SET @sqlstring='INSERT INTO TESTDATA SELECT *,''' + CONVERT(VARCHAR(12), @dtRptDate, 101) + ''' as RptDate FROM OPENQUERY(Ora1,''SELECT * FROM TESTDATA WHERE VALUE_DATE = TO_DATE('''''+ CONVERT(varchar(12),@dtRptDate,101) + ''''',''''MM/DD/YYYY'''')'')'
                                                              
EXEC sp_executesql @sqlstring

Example of the date in Oracle:
9/7/2007 9:54:43 AM
9/7/2007 9:51:54 AM
9/7/2007 9:50:30 AM
9/7/2007 2:00:24 PM

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-18 : 00:50:17

I'm assuming your query is not finding any matches because of the conversion (TO_DATE) may be clipping the date and comparing 09/07/2007 00:00:00 instead of actually comparing just the date part. You say your openquery works? Can you show a couple of records returned by it?

Not having access to an Oracle server, I'll suggest some options:
1. try using a date comparison function like datediff (if oracle has one) instead of simply "VALUE_DATE ="
2. instead of comparing against @dtRptDate, create two variables @dtRptDateStart and @dtRptDateEnd and compare VALUE_DATE is between them
eg

declare @dtRptDateStart  as DATETIME
declare @dtRptDateEnd  as DATETIME
set @dtRptDateStart = '09/07/2007' + ' 00:00:00'
set @dtRptDateEnd = '09/07/2007' + ' 23:59:59'

also not sure why you're using DATETIME instead of just a string variable (but you may have a good reason)
also not sure why you're using format 101 instead of 120 (but you may have a good reason)

Let us know how you get on

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-21 : 00:31:18

rrb, thanks for your response, I tried your code sample it run but did not return any record. So created another code, which is shown below...This one should work but I getting an error:

CODE:

declare @dtRptDate  as DATETIME
set @dtRptDate = '09/07/2007'

DECLARE @dtRptDatePlusOne datetime
SET @dtRptDatePlusOne=dateadd(d,1,@dtRptDate)

DECLARE @sqlstring Nvarchar(4000)


--** QUERY AND LOAD DATA **--
SET @sqlstring='INSERT INTO TESTDATA SELECT *,''' + CONVERT(VARCHAR(12), @dtRptDate, 101) + ''' as RptDate FROM OPENQUERY(Ora1,''SELECT * FROM TESTDATA WHERE VALUE_DATE >= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDate,101) + ''''',''''MM/DD/YYYY'''') AND VALUE_DATE <= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDatePlusOne,101) + ''''',''''MM/DD/YYYY'''')'')'
                                                              
EXEC sp_executesql @sqlstring

ERROR:

LOADING TESTDATA
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Do you know what I doing wrong? Does anyone know? Please advice. Thanks.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-21 : 01:48:01

hi pras

my code was only showing by example how to set up a date range - you were meant to include it in your existing code. No matter. Your version would be equivalent if you used a strict less than on your end date - rather than <= as you have done.

There are several options for where your code could be failing. Firstly, in your insert into TESTDATA - what is the column definition in the TESTDATA table that RptDate is being inserted into - date or varchar? You are inserting a varchar - so check that it's not expecting a date.

ie

SET @sqlstring='INSERT INTO TESTDATA SELECT *,''' + CONVERT(VARCHAR(12), @dtRptDate, 101) + ''' as RptDate FROM OPENQUERY(Ora1,''SELECT * FROM TESTDATA WHERE VALUE_DATE >= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDate,101) + ''''',''''MM/DD/YYYY'''') AND VALUE_DATE <= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDatePlusOne,101) + ''''',''''MM/DD/YYYY'''')'')'

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-21 : 05:45:47

quote:
Originally posted by rrb

hi pras

my code was only showing by example how to set up a date range - you were meant to include it in your existing code. No matter. Your version would be equivalent if you used a strict less than on your end date - rather than <= as you have done.

There are several options for where your code could be failing. Firstly, in your insert into TESTDATA - what is the column definition in the TESTDATA table that RptDate is being inserted into - date or varchar? You are inserting a varchar - so check that it's not expecting a date.

ie
SET @sqlstring='INSERT INTO TESTDATA SELECT *,''' + CONVERT(VARCHAR(12), @dtRptDate, 101) + ''' as RptDate FROM OPENQUERY(Ora1,''SELECT * FROM TESTDATA WHERE VALUE_DATE >= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDate,101) + ''''',''''MM/DD/YYYY'''') AND VALUE_DATE <= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDatePlusOne,101) + ''''',''''MM/DD/YYYY'''')'')'

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Hello rrb,

Thanks for your help, you are correct, the problem has to do with the column dtRptDate. The dtRptDate column is of a "datetime" data type. I tried to change CONVERT(VARCHAR(12), @dtRptDate, 101)to convert(datetime,@dtRptDate,101)and it still did not work, but when I took the column out it works! Therefore I know the query works, but I need the dtRptDate field. Do you know how I can add the dtRptDate without any problems? Please advice. Thanks.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-21 : 09:05:27

Hi pras,

change SELECT *,''' + CONVERT(VARCHAR(12), @dtRptDate, 101) + ''' as RptDate
to
SELECT *, CAST(''' + CONVERT(VARCHAR(12), @dtRptDate, 101) + ''' AS DATETIME) as RptDate

try it..

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-21 : 23:28:14

Speaking of carriage returns, do you think you could format your code so we don't have to scroll a mile to the right to see it all?

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources