I tried to find it on the Internet, but was unsuccessful. If someone can help me with a select statement that will return JUST the current month and year or day and month. I would really appreciate it.
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
I am trying to calculate the work experiance of an employee. I am using the function DATEDIFF for that. For eg:
Code SnippetDATEDIFF(yy,JoiningDate,GETDATE())
But this will display the date difference in years, if I use mm, instead of yy then I will get the result in months.
But I want to dislay the result as a combination of year and month, like if an employee has 13 months of experiance then i want to get the result as 1.1 years. Is that possible using SQL? Please help. All your helps will be appriciated.
hi allcan somebody tell me how can i insert todays date in sqlserver 2000 in dd/mm/yyyy format...i knw we use getdate but how exactly..plz write the code for me...i have datetime as datatype for my column name.....i want it in this format because i am using a datetimepicker to compare values while retreiving the data..and this datetime picker is taking dd/mm/yyyy format so i want to insert records in that format...plz helpthanks in advance
The reason I see is the assignment to a variable of a different data type. Is there a way to preserve the date format when I assign the value to a character field?
Does getdate() always returns date values in a standard format or it changes with the system date settings. I want to get 200110 for october 2001 and want to use getdate() for this purpose.
I'm trying to return the month part of the current date, along with the month number. I want it to return:
01 - January 02 - February, etc
It's fine for October to December, but for the other nine months my code returns 1 - January and I want to be sure the leading 0 is added. How can I do this? Here is my current code:
CONVERT(nvarchar,MONTH(Getdate())) + ' - ' + DATENAME(MONTH,Getdate()) as MonthName
I want to build a query that takes the max (or most current) DateAtMidnight from a table with all fields. This query qould ultimately be used for SSIS packages instead of formulating variables and other tables.
For simplicity, I started with a basic query.
Select DateAtMidnight, Field1, Field2 From Sales_Invoices
I cannot find a good example of how to use getdate() -1 day. Please assist.
,convert(varchar,getdate(),101) as [CONFIRMATION_DATE!1!REPORT_DATE] The above displays as 8/26/2006, anyway you can convert that to a long format in the SP? I.E. August 26, 2006 Thanks.
When I wrote "select getdate() " at SQL Query then result appear : 31 Desember 2001
But my server date/time setting at control panel is 18 pebruary 2002.
If I restart that SQL Database than select getdate() is the same with Server date/time.
Why select getdate() at sql server is different with server date/time at control panel ? is there any way to solve this problem without restart that database ?
I am trying to format the datetime field in my grouping. Right now I am using the expression =Month(Fields!TestDateTime.Value) in the edit group properties and it returns (for example) May 01 for the group. I would like it to show up as May 2007 instead. Would someone be kind enough to post what the correct expression should be?
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I am using the following expression to place the month in the heading of a column.
=MonthName(Month(DateAdd("M",-1,Now)))
The expression above gives me the previous month, but I need to have it as "Jan", not "January".
expression that will give me the three character month name instead of the full month. I've tried substituting the "M" with "MMM", but get an error. I've also tried "Mon", but again, I get an error.
I have to color format the bars in set of 4 colors. I have used SWITCH statements for other charts and it worked. But here in this case since it is a date field, I am getting error.
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Given any datetime value, I need SQL to derive the last day (numeric) for the month in the datetime value..
eg. "2001-06-22 14:24:56.563" --> Last numeric day of June 2001 is 30 eg. "2000-02-06 23:11:05.323" --> Last numeric day of Feb 2000 is 29 eg. "1999-02-07 08:34:10.037" --> Last numeric day of Feb 1999 is 28
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 "
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate()) this query is returning my data between 1(jan) and 3 (mar) if i change my query to get all datas between nov to mar select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate()) this query is not returning data since the month between 11 to 3 Guys,Help me out to correct this syntax.