[Resolved] Date and time select

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 2005 Forums

Transact-SQL (2005)

[Resolved] Date and time select

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-10-23 : 16:03:07

I have a sales ticket table where I need to select records by date and time. Need to be able to group "from date/time" as lower limit and group "to date/time" as upper limit. I tried below but it does not produce results even I have have data in the table.

select * from salestkt where (ticket_date >= '2008-10-20' and ticket_time >= '06:00:00 AM') and
(ticket_date <= '2008-10-22' and ticket_time <= '06:00:00 AM')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 16:14:48

Try this, I assume your columns are varchar:

where convert(datetime,ticket_date+' '+ticket_time) >= convert(datetime,'2008-10-20' + ' '+'06:00:00 AM')
and convert(datetime,ticket_date+' '+ticket_time) <= convert(datetime,'2008-10-22' + ' '+'06:00:00 AM')

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 16:17:15

the reason is because query is confused on

ticket_time >='06:00:00 AM' and ticket_time <='06:00:00 AM'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 16:26:44

I don't think so.
Look at the parentheses...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-10-23 : 16:27:42

In the table, ticket_date is defined as type "date" and ticket_time is defined as type "time"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 16:28:26

In MS SQL Server????

No, you're never too old to Yak'n'Roll if you're too young to die.

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2008-10-23 : 16:31:07

Not sure what you are looking for but if you need it grouped by dates, this is how it should be done

Select min(SomeData) as mindata, max(somedata) as maxdata, recordID from tableName group by recordID

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 16:36:04

quote:
Originally posted by webfred

I don't think so.
Look at the parentheses...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

(A and B) AND (C and D) = A and B and C and D .... = A and D and B and C

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 16:42:53

Can someone correct me please if i'm wrong?
In MS SQL Server there is not type DATE and no type TIME!
So what?

No, you're never too old to Yak'n'Roll if you're too young to die.

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2008-10-23 : 16:43:43

You are correct webfred.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-10-23 : 19:12:09

This is actually an Advantage data base but uses std sql syntax. If I select just from and to date it works fine. If I select from and to date and either from time or to time it works fine as well. It's when I use "06:00:00 AM" on both sides I have a problem. Also if my from time is "06:00:00 AM' and to time is "10:00:00AM" it seems to work. Thank you.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 19:37:19

That's why we avoid to have date and time in separate columns.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 03:46:55

quote:
Originally posted by webfred

Can someone correct me please if i'm wrong?
In MS SQL Server there is not type DATE and no type TIME!
So what?

No, you're never too old to Yak'n'Roll if you're too young to die.

They are available from SQL Server 2008 onwards

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-24 : 03:47:59

quote:
Originally posted by snufse

In the table, ticket_date is defined as type "date" and ticket_time is defined as type "time"

Then you are not using SQL Server 2005
Isnt it?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 03:51:54

Maybe "date" and "time" are userdefined dataypes?

WHERE	ticket_date + ticket_time >= '2008-10-20 06:00:00 AM'
	AND ticket_date + ticket_time < '2008-10-22 06:00:00 AM'

E 12°55'05.63"
N 56°04'39.26"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-10-24 : 08:21:52

[code]select * from salestkt WHERE ticket_date + ticket_time >= '2008-10-20 06:00:00' AND ticket_date + ticket_time < '2008-10-22 06:00:00'[/code]

Error:
poQuery: Error 7200: AQE Error: State = S0000; NativeError = 2124; [Extended Systems][Advantage SQL Engine]Invalid operand for operator: + -- Location of error in the
SQL statement is: 30

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-24 : 08:30:47

quote:
Originally posted by snufse

select * from salestkt WHERE ticket_date + ticket_time >= '2008-10-20 06:00:00' AND ticket_date + ticket_time < '2008-10-22 06:00:00'

Error:
poQuery: Error 7200: AQE Error: State = S0000; NativeError = 2124; [Extended Systems][Advantage SQL Engine]Invalid operand for operator: + -- Location of error in the
SQL statement is: 30

http://www.sybase.com/products/databasemanagement/advantagedatabaseserver

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-24 : 09:00:44

I found something like this in a german forum, maybe it helps:


select * from salestkt WHERE 
Convert(Convert(ticket_date ,SQL_VARCHAR)+ ' ' + Convert(ticket_time ,SQL _VARCHAR), SQL_TIMESTAMP) >= '2008-10-20 06:00:00' 
AND
Convert(Convert(ticket_date ,SQL_VARCHAR)+ ' ' + Convert(ticket_time ,SQL _VARCHAR), SQL_TIMESTAMP) <= '2008-10-22 06:00:00'

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-10-24 : 11:29:33

After playing and trying, got this to work:

SELECT * FROM SALESTKT 
      WHERE (((TICKET_DATE > '2008-10-20') AND (TICKET_DATE < '2008-10-22'))
      OR ((TICKET_DATE = '2008-10-20') AND (TICKET_TIME >= '06:00:00'))
      OR ((TICKET_DATE = '2008-10-22') AND (TICKET_TIME <= '06:00:00')))

Thank you for all your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources