Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2686
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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