SQL Server 2012 :: DateAdd From Max DateValue Column

Jan 8, 2014

I am trying to generate a @StartDate and @EndDate. The @StartDate would be the 1st of January and the year is dependent on the @EndDate.

The @EndDate would be
select max(DateValue) from dbo.testtableThe column DateValue in the testtable contains dates such as 2013-12-09, 2013-12-15.

What I am trying to accomplish is if the @EndDate is populated by select max(DateValue) from dbo.testtable returns '2013-12-15' as the max(DateValue). I want the @StartDate to '2013-01-01' because the year of the EndDate is 2013.

So something along the lines of
@StartDate = DateAdd(yy,?,@EndDate)

View 2 Replies


ADVERTISEMENT

Column Label Using Dateadd Function

Mar 7, 2008

I currently have this statement:

CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,

I would like to return however instead of column name 'MonthLessEleven' the actual month and year that is -11 from getdate().

I've tried the month(dateadd("m',-11, getdate())

but it returns syntax errors. I'm doing this in Visual Studio. Could anyone help me out? Thanks.

View 10 Replies View Related

Null For A Datevalue Field

Mar 26, 2008

Hello all,
Can some one help me with this issue.  It seems to be a common question and I have tried a lot of options but nothing seems to work for me. 
I have a text box and this is linked to ajax calendar extender.  They get a date and I am inserting/updating the date in a sql 2000 database using a stored procdure.
This is the code I use to grab the data from my web form 
If txtTrimsEntryDate.Text = String.Empty Then
_oTrim.TrimsEntryDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.TrimsEntryDate = CDate(txtTrimsEntryDate.Text)
End If
If txtEstCompletion.Text = String.Empty Then
_oTrim.EstCompletionDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.EstCompletionDate = CDate(txtEstCompletion.Text)
End If
If txtProjectClosedDate.Text = String.Empty Then
_oTrim.CloseDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.CloseDate = CDate(txtProjectClosedDate.Text)
End If
_oTrim.InsertOrUpdateAProject()
 
 
This is the code for the stored Procedure
Dim oSql As clsSqlServer = Nothing
oSql = New clsSqlServer(AppSettings(_dbName))oSql.AddParameter("@projTrimsEntryDate ", _trimsEntryDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@projStartDate", _startDate, SQLDataType.SQLDateTime, ParameterDirection.Input)oSql.AddParameter("@estCompletionDate", _estCompletionDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@closeDate", _closeDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
 Now when it inserts or updates if the value is null then it inserts "01/01/1900" in the database.  I want the value to be Null in database.
Is there a way to do this.   
 
 
 
 

View 3 Replies View Related

DateID To DateValue Lookup

Jan 7, 2008

I have two tables. The first consists of three columns that have dates in in the format of mm/dd/yy.

The second table is a Date dimension which has DateID and DateValue. DateID is a primary key and DateValue contains the actual date in the format of mm/dd/yy.

I'm having trouble with SSIS to use a lookup component that will lookup matching date values for the first table using the Date dimension and spit out DateIDs instead of DateValues.

Example:



Code Block
TABLE1
Date1 Date2 Date3
01/01/08 01/02/08 01/03/08

TABLE2
DateID DateValue
1 01/01/08
2 01/02/08
3 01/03/08

After using the lookup component, I'd want it to spit out in new columns

DateID1 DateID2 DateID3
1 2 3




Any help would be greatly appreciated!!

View 10 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 Replies View Related

My DateAdd() Problem In Sql Server 2000

May 20, 2006

hello all !!!
i have a table having fields
table _ project  1)Duration 2)Enddate (null by default) these 2 fields
now i want to set constraint that
Enddate should be equal to Duration values from the dat  User inputs in Duration field data.... .... Enddate = todays date + Duration (int)
i tried
1)DateAdd(days,project.Duration,Getdate())
2)DateAdd(dd,project.Duration,Getdate())
please help me !!!!
 
 
 
 

View 2 Replies View Related

SQL Server 2014 :: DATEADD - Grouping By Week

Aug 25, 2015

We have bee using something like the following to get the Monday for the specified date.

However, we came across a scenario where this is not working

DECLARE @dt AS datetime
SET @dt = '2/19/2006'
SELECT DATEADD( ww, DATEDIFF( ww, 0, @dt),0)

It had seemed to be working as expected, but we recently came across a problem where The above is returning 2/20, not 2/13

View 9 Replies View Related

SQL 2012 :: Why Does Server Consider A Value To Be A Column Name

Jul 22, 2015

This should be a really simple, straight forward query, but apparently it's not. The error I get is "Invalid column name 'A'. I've put it in quotes and not in quotes and I left spaces around the equal sign and I've eliminated them. What's wrong with the SELECT statement below?

SELECT * from firms WHERE firm_id= 100002
AND active_firm = 1 AND firm_id != 100092
AND trust_id = A;

View 6 Replies View Related

SQL Server 2012 :: Need To Select A Column Without 0

Jun 21, 2014

I have a table like below

create table #temp
(
valid_id int not null,
valid_no varchar(50)
)

10001
20002
30011
40012

i need to select the valid no without '0' lie 1,2,11,12, how do i?

View 7 Replies View Related

SQL Server 2012 :: Get Row Value As Column Header

Dec 21, 2014

I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.

UIDParam_ID Parameter Param_Threshold
1P1 Parameter1 NULL
4P2 Parameter2 NULL
6P3 Parameter3 NULL
7P4 Parameter4 NULL
8P5 Parameter5 NULL
9P6 Parameter6 NULL

The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.

UIDSiteIDP1P2P3P4P5P6P7P8
1721177503107NULLNULLNULL
27227129537710NULLNULLNULL
372897125851113NULLNULLNULL
4726881252997NULLNULLNULL
5727867502612NULLNULLNULL
673071312567107NULLNULLNULL
77214127535911NULLNULLNULL

I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.

UIDSiteIDParameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7 Parameter8

17211
27227
37289
47268
57278
67307
77214

I have attached the screenshot of the data for reference. I am not that much aware of sql queries.

[URL] .....

View 9 Replies View Related

SQL Server 2012 :: Select Value Then Must Put In Column

May 6, 2015

I am trying to select leveling; I have table like this;

FACC_WID FACC_BKEY FACC_CODE FACC_DESC FACC_CODE_DESC FACC_DESC_CODE FACC_H1_L5_CODE FACC_H1_L5_DESC FACC_H1_L5_CODE_DESC FACC_H1_L5_DESC_CODE FACC_H1_L4_CODE FACC_H1_L4_DESC FACC_H1_L4_CODE_DESC FACC_H1_L4_DESC_CODE FACC_H1_L3_CODE

[Code] ...

Now I need to make 5 levels like this:
1
10
100
1001
100100

When column FACC_CODE have 1 char then its a level one so I put this in FACC_H1_L1_CODE..

I this possible in (SSIS)?

View 1 Replies View Related

SQL Server 2012 :: How To Get A Sum Of One Set Of Data In Same Column

Sep 6, 2015

I put on the site excell file where I explained what I want to get . It is easier to explain through an example .

THIS IS MY INPUT DATA:

ACCOUNTS CODE ACCOUNTS NAME TOTAL

0 CLASS 0
011 Synthetic group
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
022 Synthetic group
022-1011 Some name 1 101 $

[code]...

View 9 Replies View Related

SQL Server 2012 :: Group By Column As Sub Header

Jan 28, 2014

I have a table which I would like to format like so:

From this:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED

[Code] ....

Just as a single varchar(max) field.

View 9 Replies View Related

SQL Server 2012 :: More Than 8000 Chars In A Column

Jan 30, 2014

Is there any possibility to store more then 8000 chars in a column?

View 1 Replies View Related

SQL Server 2012 :: How To Get Column Used In Where Condition In A Procedure

Apr 16, 2014

Is there any way to get the Columns Used in where condition in a procedure using SQL?

View 1 Replies View Related

SQL Server 2012 :: How To Check Which Row / Column Truncated

May 16, 2014

Table Temporary (Staging) - 400.000 rows and 200 columns.

When i done insert from temporary table to target, i have this:

Msg 8152, Level 16, State 13, Line 9

String or binary data would be truncated.

The statement has been terminated.

How can i check which row/column truncated?

View 9 Replies View Related

SQL Server 2012 :: How To Update One Column Value To Another In Same Table

Jun 21, 2014

my table payment_details structure is

payment_id payment_code
1 null
2 null
3 null
4 null

here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query

update payment_details
set payment_code = payment_no
where payment_code is null

but it shows subquery error?

View 3 Replies View Related

SQL Server 2012 :: Rounding UP Values In Column

Nov 28, 2014

I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.

SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000

View 1 Replies View Related

SQL 2012 :: Aliasing Column Names In Server

Jan 6, 2015

Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.

View 2 Replies View Related

SQL Server 2012 :: Drop And Update Column?

Feb 16, 2015

I am looking for standard sql code for below 2 concern.

1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

2-I want to update all the table where table name start with ArchiveBbx

example:-

Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

View 3 Replies View Related

SQL Server 2012 :: Compare Column In Two Tables?

Apr 20, 2015

i have two tables.

Table A
IdName
101Dante
102Henry
103Harold
104Arnold

Table B
NumberName
102Dante
107Gilbert
109Harold
110Arnold
106Susan
112Marian

I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.

Table C
Col1Col2
HenryTable A
Gilbert Table B
Susan Table B
Marian Table B

using below logic to get the values from tables..

select
t1.columnA
, t2.*
from
table1 t1
join table2 t2 on t2.columnB = t1.columnA

View 9 Replies View Related

SQL Server 2012 :: Get Splitted Column Using Function

Apr 21, 2015

I am having staging table with separted by '¯'.I want to split the data with given number .i have given 31 means my main table have 31 column. it should handle the less or more column.

declare @TempTable as Table (Id int identity, sampleData nvarchar(500))
insert into @TempTable (sampleData)
select 'B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯' union all
select 'B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯'

[code]....

View 9 Replies View Related

SQL Server 2012 :: Getting String Out Of Data From A Column?

May 7, 2015

I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below

Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information

sample data from system_id_information column

########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>

[Code] .....

I am looking output of two columns, which are bolded

product_id snumber
654081-B21 MXQ43905SW

for serial number this is common

before string :HostSystemIdentificationInfo"><identifierValue>

and after string </identifierValue><identifierType><label>Service tag

and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies

View 9 Replies View Related

SQL Server 2012 :: Normalizing A Column Containing Lists

Aug 20, 2015

CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))

INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')

The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.

The results I need are:

CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40

I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?

View 1 Replies View Related

SQL Server 2012 :: Comma Separated Column Output

Nov 19, 2013

I need a query which results the output as comma separated

for example ,

select name from tablename;

say for example this produces the following,

Name
Jack
John
Smith

I need the follwoing,

Jack,John,Smith

View 2 Replies View Related

SQL Server 2012 :: Display Data In Blocks (Column Name)

Jan 29, 2014

I want to display data in block. Below is the Create,Insert Script and format of desired output.

CREATE TABLE [dbo].[Test_AA](
[CounterpartID] [varchar](2000) NULL,
[CounterpartName] [varchar](2000) NULL,
[SATName] [varchar](2000) NULL,
[NameOfBO] [varchar](2000) NULL,

[Code] ....

Result Should be like This :

ID:17388
Name:Scottie Berwick
SATName:Scottie Berwick
NameOfBO:
SATAccountNumber:

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: Dropping Rows With Duplicate Column Name

Feb 13, 2014

I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.

Select
Distinct
TestID,
TestDate,
TestName

From third.test

I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....

View 1 Replies View Related

SQL Server 2012 :: Remove Non Alpha Chars From A Column

Mar 1, 2014

How to remove non alpha chars from a column ?

I googled the following code
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

[code]....

View 9 Replies View Related

SQL Server 2012 :: How To Get Email Address From Varchar Column

Mar 11, 2014

i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.

dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk

Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;

dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;

dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;

dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;

View 4 Replies View Related

SQL Server 2012 :: How To Find A Missing Sequence In A Column

Mar 20, 2014

Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )

insert into Sample

select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union

[code]....

select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.

View 8 Replies View Related







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