I need a where clause like
Where field=getdate()
but I can't get it to work because the field is datetime so there is never an exact match. Do I need to break out each part just to compare today's date to the 'date' of getdate()
This is a cross-post from the Office PPS Planning site: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3379691&SiteID=17 I was hoping there may be some additional MDX resources here.
I'm trying to determine if the currentmember of the time dimension is > or < the value of today. I want to use this to change the the value of a member that I display on columns from actual (for prior to today) to forecast (after today). I have this MDX:
with
member [measures].[PYCY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())-1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)))
member [measures].[CYNY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)))
member [measures].[NYNY2] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+2)))
member [measures].[test2] as Format(Now(), "yyyyMM")
member [measures].[test4] as [Time].[Day View].CurrentMember.Member_Key
member [measures].[diff] as DateDiff('m', Format(Now(), "yyyyMM"),[Time].[Day View].CurrentMember.Member_Key)
select
StrToSet('{' + Generate(Descendants(
{strtomember('[Time].[Day View].[year].&[' + [measures].[PYCY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[CYNY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[NYNY2] + ']') }
, [Time].[Day View].[Month]), [Time].[Day View].CurrentMember.UNIQUE_NAME, ", ") + '}')
* {[measures].[test2],[Measures].[test4],[measures].[diff]}
on 0,
[Scenario].[All Members].members
on 1
from [SalesForecasting]
Here is the result for 2 months:
October FY2007
October FY2007
October FY2007
November FY2007
November FY2007
November FY2007
test2
test4
diff
test2
test4
diff
All
200805
200610
#Error
200805
200611
#Error
None
200805
200610
#Error
200805
200611
#Error
Actual
200805
200610
#Error
200805
200611
#Error
Forecast
200805
200610
#Error
200805
200611
#Error
Override
200805
200610
#Error
200805
200611
#Error
Plan
200805
200610
#Error
200805
200611
#Error
The error is: #Error Execution of the managed stored procedure DateDiff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..
Why can't DateDiff calculate the difference between 200805 & 200610?
Thanks
I have to do a report for each hour compare yesterday each hour of sales amount their output is below, how to write a query.
desire output 08:00 am -23:00pm
shop today Time yesterday current Amt Yest Amt diff amount
001 13:00-14:00 13:00-14:00 $10000 $20000 -10000 (down)
002 14:00-15:00 14:00-15:00 $10000 $15000 500 (up)
Time as at HH:MM
Yesterday Total $20000
today Total $35000
I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)
I write code
select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)
it will like
select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)
→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30
but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code
pleae help... thank you very much
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
Please i need an exmple of ur solution, thanks :)
I'm using some files to show certain pages on certain date for an example
File name : aa.doc
start date: 10/02/00
end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page
Here is my stored procedure:
"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "
Thankx a lot
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
hey all.
I'm VERY new to SQL and this is throwing me a tad...
I want to do the following
SELECT T1.ID, T1.No
FROM MYDB.DB.T1 T1
WHERE T1.No = TODAY
i only want to return the values that equal No being today.
am i on the right track?
hello,
I need to get everything from today to the end of the month. So for example, if it was august 3rd, I would need everything from August 3rd to August 31st. I can't hard code the date in, so is there any way to do this dynamically? This is what I have by hardcoding it in:
select unit.dtavailable, unit.hmy from unit inner join property on property.hmy = unit.hproperty
inner join unit_status on unit_status.hunit = unit.hmy
where unit_status.sstatus = 'Notice Unrented'
and property.hmy = '21'
and unit.dtavailable > getdate()
and unit.dtavailable <= '07/31/2006'
group by unit.hmy, unit.dtavailable
hi,
How can i get today date in this format day/month/year
(in the SP of course)
thanks
Why i got "SqlException was unhandled by user code" "{"Incorrect syntax near '12'."}" ?
How can i only get people which is brithday is today?
The "rc_brithday" datatype is datetime
Thanks you
Code is below:
cmdSelect = New SqlCommand("Select rc_email From recruiters WHERE rc_brithday = " & DateTime.Now(), conPubs)
conPubs.Open()
dtrTitles = cmdSelect.ExecuteReader()
HelloI want to return the number of days between a date in the database and todaysomething likeSELECT user.fName,user.lName & " (" & (datediff(now - user.lastVisit)) & " )" FROM user I must return John Turner (38)where 38 are the days between last visit and nowthank you
View 2 Replies View RelatedI converted an MS Access db to SQL Server 2005 Express yesterday. I used FullConvert Enterprise for the conversion and it worked great. I ran several queries and saved them, and they ran fine. Today, running the same queries, I get this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'
I googled the message and found someone who had a similar problem and their answer was they were not the dbo. I checked the new database and it was owned by sa so I logged in as sa and got the same error.
Can anyone set me straight so I can get into this db?
Thanks,
Okay, 2 questions here.
First I want to query everyone with a date greater than today in the field EXPIRATION DATE. I know I can say > 2006-03-19 but I always want it to be TODAY.
Next, Is there a system field that I can search for the date a record was last updated?? I make frequent changes, and I want others to see what date a record was last updated. If not, can I tell the system to mark a field "YES" when I make changes?
I use phpmyadmin. I do not have server access, just through PHP.
Thanks!
TV
...The TIger has ROARED
I am trying to get the Date field in my SQL Query to only return.
Date that match todays date.
this is waht I have but it produces an error.
WHERE
(EsnAsset.EffectiveDate LIKE DateTime.Now)
You can use a Select top @variable in sql server 2005:Thus:--'Throttle' the result set:Select Top (@MaxBatchSize)KeyID, LId, ArrivalDt, CaptureDt, Lat, LongFrom GPSDataOrder By CaptureDt DescMakes messing with Set Rowcount *so* redundant :-)
View 1 Replies View RelatedI want to create and expression that basically says if a datetime value is today minus 2 days then do another formula.
Right now it looks like this...
Code Snippet
=iif(Fields!Channel_Id.Value="SFE1" and Fields!Report_Date.Value=Today-2,0,(Fields!Total_Apps.Value-Fields!total_apps_null_status.Value))
Basically, it spits an error saying [BC30452] Operator '-' is not defined for types 'Date' and 'Integer'.
Should this be done with DateDiff? if so, how can you specify two days ago?
Thanks much
C-
I am saving files in SQL Server 2005 with a datetime field called news_date_time and I want to display all today's records regardless of the record time.
I tried this code but didn't work..
[code]
SqlCommand sql_command = new SqlCommand("SELECT * FROM files_news WHERE news_date_time = TODAY ORDER BY news_date_time DESC", sql_connection);
[/code]
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
Hello , i want to writ a query that returns ruslts for today's date only,
How to do it? i tried to filter the results using Now() function but it did`t work, any help please?
Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John
View 7 Replies View RelatedI want to retreive users that had their birthdays 7days before today and will have their birthday 7 days from now (so 14 days in total).What would the SQL for such a thing look like?I now have ths, but that doesnt work when you are at the beginning of a month (march 1). So there must be a more clever way :)select firstname from userswhere (month(ud.birthdate)=month(getdate())) and (month(ud.birthdate) between month(getdate())-1 and month(getdate())+1 ) and (day(ud.birthdate) between day(getdate())-7 and day(getdate())+3 )
View 4 Replies View Related Hello. I have an "Events" table with a datetime column containing dates (the time is unnecessary). I want to select an upcoming event (one post) from the table where the date is either today or the day nearest to today.
Dim today As Date = Date.Now
I have declared a 'today' variable but I don't know how to use it in
the SQL query. Will I have to convert the today variable to something before using it?
Should a Label be involved? The event will be displayed in DetailsView.
Hi there,
I am trying to ascertain how many users have registered with my site today. I am using the following stored procedure:
CREATE Procedure spGetUserCountToday
As
Return ( SELECT Count(*) FROM tblUserList WHERE role= "User" AND registerDate >= GETDATE()
)
GO
However the issue is the GetDate() function will only return those that have resgistered at the exact moment the query is run.
How can I change the GetDate to return only those users who have registered in??
Thanks in advance,
TCM
I have to select rows based on if the transaction date = todays date.The column is defined as numeric 8 with 0 decimal.how to code for todays date with such a column?
View 8 Replies View RelatedI want SQL to look at a date field and sort the data by todays date and greater. Even though there may be some data older than today. I've tried something like this but not working
order by SSD_SED >= GETDATE()
Where the date field is SSD_SED.
I need to control DOF (date of order) which data type is datetime for today's date.
I use 1) or 2) but got null.
1) = getdate(),
2) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
I use
between '2/28/2008' and '2/28/2008'
will get result.
How to get today's date?
select no_dossier from dbo.membre where date_MAJ = GETDATE()select no_dossier from dbo.membre where date_MAJ = '2005-07-21'Both should give me the same result, yes or no?thanks
View 1 Replies View Relatedselect no_dossier from dbo.membre where date_MAJ = GETDATE()select no_dossier from dbo.membre where date_MAJ = '2005-07-21'Both should give me the same result, yes or no?thanks
View 11 Replies View RelatedHello,I am attempting to build a MS SQL query that will return data from"today"; today being current day 8:00AM-10:00PM today. My goal is toreturn the data from a table that is written to throughout the day, thequery will provide the current grade of service in our call center.I am having difficulty defining my where clause:- I can accomplish my goal my statically defining my 'date between' asthe actual date and time (not ideal)- I can accomplish the second part of my date using CURRENT_TIMESTAMP;but I am unable to define the starting pointHere is where I am thus far:/* We are going to count the total calls into each queue from start ofbusiness today (8:00AM) to now */select COUNT(Result) as "Total Sales Calls Offered" fromdbo.QueueEncounterwhere Direction='0'andQueueID='1631'and/* This is where I get lost */Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())Clearly the last line returns zero as there are no calls between thesame date range. How can I add to that line, or write this to work?Any thoughts?Thanks for the help.-Chris
View 4 Replies View RelatedI converted an Access db to SQL Server 2005 Express using FullConvert Enterprise and the conversion went well. I ran a few queries. When I logged in today and run the same exact query (saved it) I get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblTXMup'.
I googled the error and someone in another forum said to check ownership, that it would throw that error if another user who was not dbo tried to run a query. I checked and the owner is sa. I logged in as sa and get the same error.
Anyone have an idea what happened?
I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date
View 2 Replies View Related