SQL Server 2012 :: Just Get Month And Date Only In A Query Without Year?

Mar 18, 2015

This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Converting Year And Month Varchar To Date

Jan 31, 2014

I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.

Year Month Productname
2013 11 ACB
2013 11 CDE

I would now like to add another column called date and store these Year Month as a date to my existing table

Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01

Is there a way I can do it for all the columns of the existing table ??

View 3 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

SQL Server 2012 :: Calculate Customers Age And Value By Month And Year?

Sep 22, 2014

What's the best way to calculate a customers age and value by month and year?

I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:

select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month

[Code] .....

It seems to work, but any better way to achieve this?

View 1 Replies View Related

SQL Server 2012 :: Comparing Data For A Certain Day Of Week By Month For Every Year

Oct 21, 2014

I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.

View 9 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

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

View 9 Replies View Related

SQL Server 2012 :: Create View To Allocate Amount Per Month - Financial Year

Mar 21, 2015

I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below

Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.

25,000 June 2015

View 7 Replies View Related

Getting Date In Day/month/year Instead Of Month/day/year

Feb 2, 2004

I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.

I'm using a DatePart function that converts my date:


CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END


This returns te american version, can anyone help me to get this to convert the UK way.

Thanks

View 3 Replies View Related

Extract Date,month, Year From The Date Getting From Sql Table

Apr 14, 2008

Hello All,
i have three textboxes in a page and i want fill those textboxes  with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne

View 3 Replies View Related

See If Date Is Same Year And Month And Day

Mar 8, 2008

Hi,

I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

View 9 Replies View Related

Want To Concatenate Year And Month To Get A Date

May 14, 2004

Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View 5 Replies View Related

Want To Concatenate Year And Month To Get A Date

May 14, 2004

Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View 7 Replies View Related

Last Year Month To Date Function

Feb 16, 2006

I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Transact SQL :: How To Get Month And Year For A Given Date

Jun 3, 2015

I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.

how to modify my where clause so that it runs regardless of even if its Jan ?

View 6 Replies View Related

Only Month And Year From Date Time

Apr 21, 2008

Hi All,

How do I show only month and year from datetime data type field?

As a example; If the date is 01-10-2008.
I wanna show the date in my report as Jan - 08

Thanks

View 17 Replies View Related

How To Get Month And Year From A Date Which Is Of Type Varchar(20)

Dec 2, 2011

In my table I am using varchar to save a date like this 2-dec-2011. Now I have a requirement where i need to select employee who where present in a month so for that I need a query so that i can pull out all the employees who are present in dec like :

Code:
select * from emp_attendence where date = 'dec-2011'

But this returns nothing ...

View 8 Replies View Related

Extract Month & Year From Date Column

Feb 6, 2008

hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

View 11 Replies View Related

Display First Date If Split Into A Year And Month Column?

Mar 27, 2012

How can I find the first date if the date is split into a year and month column?

I thought of using the Min function, which would work for the year column, but it would probably just return a 1 everytime in the month column.

View 2 Replies View Related

Creating Birth Date From Day, Month, Year Fields

Nov 14, 2007

I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)

They are populated like this (1985, 12, 21)

I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.

The code

UPDATE DOB
SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

Returns 6/11/1905 for 1959,11,17

View 5 Replies View Related

How To Get Date Difference In Terms Of Year,month,days

Mar 31, 2008



I am using oracle 10G DB as back end.I have two date fields in a table.


1)premium_paying_start_date

2)premium_paying_end_date


i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).

The difference should show the year,month and no of days difference.

For example :

premium_paying_start_date : 14-10-1984

premium_paying_end_date : 01-03-2008

Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)


So please give me the solution for this.

View 6 Replies View Related

Transact SQL :: How To Concatenate Year / Month And Day Into Actual Date

Jan 10, 2012

I am working with a SQL Server database that was set up to store the year, month, and day in separate columns, rather than use a single column for the date. I plan to change this so that we store dates and times using the datetime data type. Until then, for now, I need to write transact-SQL select statements to concatenate the year, month and day to create a date that can be displayed in the results window in the format yyyy/mm/dd.

I can't seem to find any built-in function in SQL Server that will let me do this. Of course, using Excel or Access, I can use the DATE() function to reconstruct the date into yyyy/mm/dd. Is there a way to reconstruct the date into yyyy/mm/dd in SQL Server? 

View 7 Replies View Related

SQL Server 2012 :: Date Table For Financial Year

Dec 23, 2014

I need to create a table which holds date information for our financial year.

I have all the dates between now and 2045 and the start of the week and the end of the week. What I also have is the first sunday of the previous week in the spreadsheet too.

Please see below attachment

What I need to autofill once I import these three dates into a database is the week and the month.

The difficulty surrounding the month is that, we start a new month on the FIRST Sunday of the month.

So dates 07/04/14 to 04/05/2014 would be month 1.

Month 2 would begin on 05/05/2014 as it is the day after the first Sunday of the month, and so on....Month 5 would start on the 04/08/14.

Need to script something that would automatically calculate the week and month for me on the basis on above, if I have the start date, end date and 1st sunday already in a table?

View 9 Replies View Related

T-SQL (SS2K8) :: Finding Previous Even Numbered Month And Appropriate Year From Given Date

Mar 25, 2014

I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.

Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013

View 2 Replies View Related

SQL Server 2012 :: Add Month To A Date

Jun 3, 2014

I am trying to add month to a date. Here is my code

declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all

[Code] ....

It is working fine. But when I am giving date '10-30-2014' it shows me the error

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

I can understand the problem that it is for the month of February. But How do I overcome the situation?

View 3 Replies View Related

To Display Month Name And Year Instaed Of Complete Date...in SSRS Reports

May 6, 2008

Hi All,
I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases for the particular month...the date should not be displayed..instead of date 2008-04-30 I have to display...like April 2008...if the date is 2008-10-20...it should display as October 2008...can anyone please help me with this...
312 KRISTI WHITE 865400 2008-04-30 2
312 KRISTI WHITE 1000264311 2008-04-30 3
312 KRISTI WHITE 1000430815 2008-04-30 1
312 KRISTI WHITE 1000660614 2008-04-30 1
312 KRISTI WHITE 1002371318 2008-04-30 2
312 KRISTI WHITE 2003722520 2008-04-30 4


Thanks
Chaitanya.

View 10 Replies View Related

Transact SQL :: Function To Find Last Date Of Month One Year Ago - RETURNS ERROR

Apr 28, 2015

I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example,  it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:

Operand type clash: date is incompatible with int

Why is this error being returned?

Here is my function:

CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
       RETURNS date
AS
BEGIN;
  DECLARE @Result date;
  SET @Result =  convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
    RETURN @Result;
END;

View 11 Replies View Related

How To Do A Year-to-date SQL Query Where Year Commences In August?

Jul 20, 2005

Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View 2 Replies View Related

Grouping My Month/year Desending By Month/year

Dec 11, 2006

i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year  of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all 
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/"  + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode   FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND  (dbo.classT.coned IS NOT NULL)", conNorthwind )

View 9 Replies View Related

Reporting Services :: Displaying Sum Of Current Month And Year To Date Without Using Two Separate Datasets?

Jun 4, 2015

I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.

This is my dataset 

SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period

[code]....

I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.

StaffId | Non Chargeable Time | Chargeable Time

CJJ | 0:20 | 4:20
     | 4:50 | 19:20
JN | 0:05 | 5:30
     | 1:30 | 25:30

The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.

I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.

View 6 Replies View Related

Reporting Services :: Date Control Does Not Allow Double Click To Move By Month Or Year

Aug 21, 2015

I have a SSRS report developed in Visual Studio 2013 and using SQL server 2012. It has a date control based on parameters which works correctly in the development environment. I can select by day AND I can move from date to date by clicking on the month name to be able to move by month or double clicking to be able to move by year. 

When the report is deployed to Internet Explorer 11 or Firefox I can only, click back and forward by one day at a time and I can not double click the date control to move by month or double click to move by year. I can type the dates into the parameter box and get the correct result. I have set the compatibility view settings in IE for my server. 

View 4 Replies View Related

Current Month && Year Query?

Jan 18, 2007

I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1.  I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the  (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1    SELECT     CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2    FROM         Users3    WHERE     (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4    case  MONTH(User_Date) when  1 then 5      (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6    else (YEAR(User_Date) = YEAR(GETDATE()))7    END8    GROUP BY CONVERT(char(10), User_Date, 101), User_Name9    ORDER BY CONVERT(char(10), User_Date, 101), User_Name 
 

View 5 Replies View Related

Reporting Services :: Show Current Month / Date And Year In Report Header In SSRS

Jun 29, 2015

How to show the CurrentMonthanddateandyear in my report header in ssrs?

1.How to show the currentdateandMonthyear exmple date format like June 29 2015 on my report header.

2.How to change the report rdl name with the same name like EmpUpdatedreportJune 29 2015.rdl ,it is possible to create and change the rdl file name with the current dateandmonth.

View 9 Replies View Related

Query The Data For Month,year Range

Apr 30, 2008

Hi,


I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.

(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved