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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
Mar 19, 2015
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies
View Related
Dec 19, 2013
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
View 8 Replies
View Related
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
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
Sep 18, 2015
I am trying to write a query that give me how many items were filed per year.
This is the error:
Conversion failed when converting the varchar value 'YEAR(1999)' to data type int.
Here is the query I am using:
SELECT COUNT (*),
CASE
WHEN ChargesFiledDate > 20141231 THEN 'YEAR (2015)'
WHEN ChargesFiledDate > 20131231 THEN 'YEAR (2014)'
WHEN ChargesFiledDate > 20121231 THEN 'YEAR (2013)'
ELSE YEAR (0000)
[Code] ...
And got the same error. I know there is probably a better way to get this data (being that I need it from 2007 to present), but don't know how to write it.
View 8 Replies
View Related
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
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
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
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
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
Aug 12, 2014
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
R222209
R222220
R222222
R222212
R221123
F707768
[Code] .....
View 5 Replies
View Related
Aug 6, 2015
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
View 4 Replies
View Related
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
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
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
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
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
Oct 30, 2014
OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.
Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))
View 1 Replies
View Related
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
Mar 19, 2007
i have this SQL table with a year, week and day-of-week field, but no field containing the actual date
is there a way (e.g. in a view of the SQL table) to transform the year/week/day combination into a date string (e.g. format dd/mm/yyyy) ?
View 7 Replies
View Related
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
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
Aug 14, 2014
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
View 3 Replies
View Related
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