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.
| Author |
Topic |
|
JezLisle
Posting Yak Master
132 Posts |
 Posted - 2008-09-15 : 03:28:48
|
I have this function in my query DATEDIFF(DAY, [Date on Waiting List], GETDATE())/ 7 AS [Weeks Waiting] and looking to find how I can change this and everytime I run the query it looks back to find the begining of the month, instead of todays date. So no matter what day I run the query in the month, it always reverts back and measures from the 1st...How is that possible to do that? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-15 : 03:29:25
|
[code]dateadd(month, datediff(month, 0, getdate()), 0)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
JezLisle
Posting Yak Master
132 Posts |
Posted - 2008-09-15 : 04:41:37
|
| I changed[CODE]DATEDIFF(DAY, [Date on Waiting List], GETDATE())[/CODE]to this below[CODE]DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)[/CODE]This error message appears when trying to execute the queryMsg 257, Level 16, State 3, Line 1Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.I dont understand what its saying, can you explain? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-15 : 08:48:22
|
dateadd(month, datediff(month, 0, getdate()), 0)the above statement return a datetime. It is the 1st of the current month. Without knowing your complete query, all i can says is you are trying to implicitly convert the result of the statement which is in datetime data type to integer data type.Show us your full query
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-15 : 08:49:54
|
quote:
Originally posted by JezLisle
I have this function in my query DATEDIFF(DAY, [Date on Waiting List], GETDATE())/ 7 AS [Weeks Waiting] and looking to find how I can change this and everytime I run the query it looks back to find the begining of the month, instead of todays date. So no matter what day I run the query in the month, it always reverts back and measures from the 1st...How is that possible to do that?
What do you want actually ? You want the 1st day of the current month ? OR ? ?Provide some sample and show us what do you want
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
JezLisle
Posting Yak Master
132 Posts |
Posted - 2008-09-15 : 10:22:53
|
| Basically, whatever day I run the query from, I'm looking for the SQL to look back to the 1st of the month and workout the difference between the Date on the Waiting list and the 1st of the month in Weeks. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-15 : 22:27:26
|
quote:
Originally posted by JezLisle
Basically, whatever day I run the query from, I'm looking for the SQL to look back to the 1st of the month and workout the difference between the Date on the Waiting list and the 1st of the month in Weeks.
The 1st of the month can be obtain byselect dateadd(month, datediff(month, 0, getdate()), 0) You want the different in Weeks ?DATEDIFF(WEEK, dateadd(month, datediff(month, 0, getdate()), 0), [Date on Waiting List]) AS [Weeks Waiting]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Always show dates from begining of Month