Query from date 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

Query from date problem.

Author

Topic

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-01 : 11:12:17

Hi,

I am trying to write a query but I think I am running into trouble with the way my dates are stored. We use dd/mm/yyyy but they are stored in a char type field because of issues we had with storing the date in this format.

Every record has a due date. Id like to pull records that are due within 10 days, but I am not sure how to write this because its not technically a datetime field.

Any help is appreciated!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-01 : 11:37:31

can you post the table structure along with few sample rows and the desired output you would like to have .. This will help us to help you

Cheers
MIK

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-01 : 11:47:34

Here is my original query, which should help you understand my structure.

SQL = "SELECT ID, Job, Customer, JobName, JValue, DueDate, Inspection, QControl, Active FROM Jobs WHERE Active='True' ORDER by Job DESC"

I would like to filter results from this query by adding an AND to pull only jobs with a DueDate within 10 days from now. Hope that helps.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-01 : 11:55:07

And DueDate Between Getdate() and dateadd(dd,10,getdate())

Cheers
MIK

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-01 : 12:06:57

quote:
Originally posted by MIK_2008

And DueDate Between Getdate() and dateadd(dd,10,getdate())

Cheers
MIK

Thank you for the suggestion. As I said though, since DueDate is technically a char field, anything I have tried including your example result in the following:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-01 : 12:37:07

ok pardon

. then try this

And convert(datetime,DueDate,105) Between convert(datetime,Getdate(),105) and convert(datetime,dateadd(dd,10,getdate()),105)

Cheers
MIK

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-01 : 12:48:47

I would stongly suggest that you change your schema to hold dates as dates!

If you had even held the dates as character in YYYYMMDD format then range searching would work.

I would alter MIK's code, to something like the following, so that it might try to use an index:


SELECT ID, Job, Customer, JobName, JValue, DueDate, Inspection, QControl, Active
FROM Jobs J
WHERE Active='True'
	AND EXISTS
	(
		SELECT *
		FROM 
		(
			SELECT 0
			UNION ALL SELECT 1
			UNION ALL SELECT 2
			UNION ALL SELECT 3
			UNION ALL SELECT 4
			UNION ALL SELECT 5
			UNION ALL SELECT 6
			UNION ALL SELECT 7
			UNION ALL SELECT 8
			UNION ALL SELECT 9
			UNION ALL SELECT 10
		) N(N)
		WHERE CONVERT(char(10), CURRENT_TIMESTAMP + N.N, 103) = J.DateDue
	)
ORDER by Job DESC

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-01 : 12:51:55

quote:
Originally posted by MIK_2008

ok pardon . then try this

And convert(datetime,DueDate,105) Between convert(datetime,Getdate(),105) and convert(datetime,dateadd(dd,10,getdate()),105)

Cheers
MIK

That seems to work great! I have always had problems with the date format with sql..

Thank you very much MIK

rtown
Yak Posting Veteran

53 Posts

Posted - 2011-03-01 : 12:54:45

quote:
Originally posted by Ifor

I would stongly suggest that you change your schema to hold dates as dates!

If you had even held the dates as character in YYYYMMDD format then range searching would work.

I would alter MIK's code, to something like the following, so that it might try to use an index:

SELECT ID, Job, Customer, JobName, JValue, DueDate, Inspection, QControl, Active
FROM Jobs J
WHERE Active='True'
AND EXISTS
(
SELECT *
FROM
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
) N(N)
WHERE CONVERT(char(10), CURRENT_TIMESTAMP + N.N, 103) = J.DateDue
)
ORDER by Job DESC

Thanks for writing this. I can say though that I have absolutely no idea what is happening with that query.. I dont understand it, and I like to fully understand what is happening if I am to implement the code.

Can you explain why this way of doing it will be better? MIK's seems to work great.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-01 : 14:08:33

well you are welcome. And as far as Ifor statement concern. Its regarding the performance gain while Searching Using Date column. if you have date column with an index then searching via date parameter would use the Index, but if you are trying to search a Char column via date parameter then such search will not use the Index if implemented on that char column (storing date info)

. Since you asked only for how to pull info as per your requirements i didn't bother to go in detail regarding its performance impact. However i would also suggest at-least and Always to store the date information in DateTime/Date Datatype field.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources