[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2823
  • Last Modified:

Type timedate is not a defined system type.

Getting error...

Type timedate is not a defined system type.

can anyone help?

thanks
SELECT * FROM orders 
WHERE timedate  >= dateadd(day, 1-datepart(day, getdate()),  convert(timedate, convert(varchar(10), getdate(), 120), 120)  )
AND timedate  < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(timedate, convert(varchar(10), getdate(), 120), 120)  ))
AND status = 'transaction authorised'

Open in new window

0
pigmentarts
Asked:
pigmentarts
  • 7
  • 4
  • 3
  • +1
2 Solutions
 
RiteshShahCommented:
do you have "TimeDate" field in Orders table? it seems you don't have that field
0
 
RiteshShahCommented:
have a look at this.



SELECT * FROM orders
WHERE timedate  >= dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  )
AND timedate  < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
AND status = 'transaction authorised'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT * FROM orders
WHERE timedate  >= dateadd(day, 1-datepart(day, getdate()),  convert(timedate, convert(varchar(10), getdate(), 120), 120)  )
AND timedate  < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()),  convert(datetime, convert(varchar(10), getdate(), 120), 120)  ))
AND status = 'transaction authorised'

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Mark WillsTopic AdvisorCommented:
In sql 2005 the data type is datetime, in sql 2008 you have date, time, datetime, datetime2 etc...

Based on the code you have, it looks like the convert should be converting to datetime not timedate.


SELECT * FROM orders
WHERE timedate >= dateadd(day, 1-datepart(day, getdate()), convert(DATETIME, convert(varchar(10), getdate(), 120), 120) )
AND timedate < dateadd(month, 1, dateadd(day, 1-datepart(day, getdate()), convert(DATETIME, convert(varchar(10), getdate(), 120), 120) ))
AND status = 'transaction authorised'
0
 
RiteshShahCommented:
Mark is absolutely right, that is what I have given in my query above.
0
 
Mark WillsTopic AdvisorCommented:
Right, see that now...

so what about :

SELECT * FROM ORDERS
WHERE timedate  BETWEEN DATEADD(month, DATEDIFF(month, 0, getdate()),0) and DATEADD(ss,-1,DATEADD(month, DATEDIFF(month, -1, getdate()), -1) + 1)


Does that make it a bit more different ? Certainly a lot faster :)
0
 
RiteshShahCommented:
Good Work mark, I was about to do the same, you beat me in this for sure :D

0
 
RiteshShahCommented:
I wonder, query given in post # 24421512. is it working? my query should work for sure given in second post of this question, before everybody. I was atleast expect assisted in this case.
0
 
Mark WillsTopic AdvisorCommented:
@pigmentarts,

I think there has been a small injustice here. RiteshShah did in fact get the SQL Query correctly coded with both instances of DateTime.

0
 
pigmentartsAuthor Commented:
what did i do?  this is what i was thinking..... if i did not split like this then sorry


RiteshShah did a wonderful job at answering my question  however the post 24421776 by Mark also worked and my query did indeed load faster.

I know that RiteshShah answered my question but I felt that both RiteshShah and Mark wills helped me in understanding and Marks query helped me to see the error of my mistake.
0
 
RiteshShahCommented:
:) yes mark, no question in split
0
 
pigmentartsAuthor Commented:
o I see what i did did now! again thanks for you help.
0
 
pigmentartsAuthor Commented:
Thank you for you help.
0
 
RiteshShahCommented:
thanks pigmentarts and Mark
0
 
Mark WillsTopic AdvisorCommented:
Happy to help :)
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now