I have a table with one column as a date field in the form of mm/dd/yyyy and I would like to create a new column on a report as 'Days Open' using the column with random dates in the past and subtracting it from the current system date. Can anyone provide any assistance. I'm very new to SQL Server. I know in Oracle u can use 'sysdate'
View 2 RepliesI have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB.
If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).
I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...
Thanks in advance.
TableA
MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 0
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
TableB
MemberId EmployerId EffectiveDate
12345 444 4/1/2006
TargetTable (TableA Updated)
MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 1
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
12345 111 1/1/2006 3/31/2006 0
12345 444 4/1/2006 4/30/2006 0
12345 111 5/1/2006 10/31/2006 0
Thanks
Suresh
I need to to get the result of the function GETDATE and converted to a simpler "mm/dd/yyyy" format in order to compare the results to another date in a table. In ACCESS the function DATE returns the format of 'mm/dd/yyyy' since I need to work with date ranges without a need for this application 'HH:MM:SS'
I have try 'TRANSFORM(GETDATE,'mm/dd/yyyy') but I keep getting errors.
I am not sure what I am doing wrong? Any help is appreciated since I need to work in SQL Server 2000.
Gratefull
Neil
I have a column(dts) in the database that will retun the date in this format:
2008-02-19 15:10:59:840
I would like it to ve convert it the this format:
Feb -19-08
or
Feb -19-2008
Thanks
Given any date, how can I change that date to be one of the next 3 year cycle dates.
These are the fixed cycle dates (always on Sep 1 on those set years)
9/1/1997
9/1/2000
9/1/2003
etc.
For example,
if the supplied date was January 4, 1998, it would convert to September 1, 2000
October 31, 2000 -> September 1, 2000
Decemb 12, 2005 -> September 1, 2006
January 1, 1995 -> September 1, 1997
Thanx,
Angel
I'm quite new to SQL Server and I have a pretty naive question.
I have a table called Company that has a field called Renewal date. I have a task that needs to be run on the first of every month to gather all companies that have Renewal Dates coming up in the next 180 days. The Renewal Date is a datetime field in Sql Server. Is there a way I can have this accomplished.
Any help is greatly appreciated.
Thanks,
I have data from an OLE DB connection in a Dataflow. This data has a datetime column. I am trying to use a derived column object to add new date data to the destination column in the destination table. I would like to change the data in the datetime column, add some time to the value and add as a new column in the destination. The expression I am trying to use is " DATEADD(Hh, 6 , datevalue). " SSIS doesnt like this expression as the datevalue data is a column. How would I accomplish this task using SSIS?
Greetings All, I was hoping that someone might be able to help me withthe following issue:table ddl:create table exchange(exchangefrom varchar(6),exchangeto varchar(6),exchangecode varchar(6),datemfrom datetime,dateto datetime,exchangerate decimal(28,10))The data in this table under normal conditons will look like:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 01/31/2004 .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My task is to calculate the days delimeted by the start and end date ofthe period which is simple enough:select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,datediff(d, datemfrom, dateto)from exchangeHowever due to circumstances beyond my control the dateto field maycontain a null instead of a valid end date!! YIKES:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 NULL .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My solution to correct the data is to populate the missing end datewith the (start date -1 day) of the next period. However, I am notsure how to do this with SQL? E.g) from the example directly above therow: USD GBP GL 01/01/2004 NULL needs to be updated to:USD GBP GL 01/01/2004 01/31/2004 and this can be done bylooking for the next period (USD GBP GL 02/01/200402/29/2004) that follows and subtracting from its start date 1 day (02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriateend date.If anyone has any insight into solving this problem I would be verythankful.Regards, TFD.
View 7 Replies View RelatedHello,
I'm trying to use SQL for data warehousing using dates for manipulating data. As SQL doesnt have date, just timedate, and if I want to compare 2 dates to obtain someones age ( ie date - date of birth = age ) I have to use a TIMEDIFF or equivelent. This is REALLY annoying. Is there anyway to use just dates without having to code extra?
Does SQL ignore the time part of a datetime column - ie can I just do a date manipulation without concerning myself with the time ( assuming the time is NOT default at 00:00:00 for every entry ) or am I stuck with treating the field as a complete datetime? It may seem a dumbass question, but as I have to do a lot of date manipulations, it is essential to know.
Many thanks,
Warwick.
I have a question on date manipulation functions and CASE statements
My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-
monthColHeader = eg 'Feb 2015'
defaultStartDate and defaultEndDate
filterStartDate and filterEndDate.
These are my conditions;-
if defaultStart and End = filterStart and End use monthColHeader for the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.
When I say use monthColHeader I mean like this;-
(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))
This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....
col1 col2 col3
1IN NULL
2FOR 1
3Small 4
4World 1
5Hj 1
6NJ 1
7Welcome 1
8So 4
9We 1
10Are 1
11Resp 3
12OIJIOJPOJPJO7
13OIJOIJ 1
14LKJ 2
15IJ 1
Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1IN NULL
2FOR1
2nd set would look something like this
3Small 4
4World1
5Hj 1
6NJ 1
7Welcome1
3rd set
8So4
9We1
10Are1
4th Set
12OIJIOJPOJPJO7
13OIJOIJ1
and the last set
14LKJ2
15IJ1
Any help is appreciated!!
Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.
I loop through that data to load a check list which also works wonderfully.
two questions based on that.
1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.
2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)
sorry for the fairly basic question. i appreciate the help.
Justin
I am totaly confused please help
I am trying to change a tring of 7 characters of the format "XXYZZZZ" to be
"XX0YZZZZ" I wonder if any body has any idea
Also how can get an out of a dattime field in the format of DDMMYYYY and converted into text.
You help is highly appreciated
Hi!
I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:
CANADA - Toronto
United States- ARIZONA
France
Argentina
United States (USA)- ARIZONA
........
........
The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:
Server: Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
QUERY
-----
select
Left(
Destinations.[Destination Name],
charindex("-", Destinations.[Destination Name])-1
)
as test
into temp2
from destinations
CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).
Hi,
I have a table that has about 12 fields and 700000 records. My client is going to send me 4 fields out of the 12 fields (1 of them will be primary key) in a text format. What is the best way I can get them updated in the table?
Please help!!
Thanks!!
I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname
crab,mike,Allen
Lota Weilly,Eric,M
My script to do this looks like
update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (longname, patindex( '%,%' , longname) + 1, patindex( '% %', longname)-patindex( '%,%' , longname)),
middle_name = substring (longname, patindex( '% %', longname) + 1, len(longname)-patindex( '%,%' , longname))
My problem is that some people actually have 2 last names, not hyphenated, but 2. Whenever I have 2 names I get the following error
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
It seems to be related to the first name, I can comment out that update and it works
Thanks
Thanks
Hi All,
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
Any help is appreciated.
Thanks.
Field1 = Dominguez Public Transport Division 03 9320 4326
how do i remove these strings in T-SQL
Field1 = Dominguez
Field2 = Public Transport Division
Field3 = 03 9320 4326
Hi All,
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
Any help is appreciated.
Thanks.
Hi, is there a way i can replace multiple white spaces & tabs into one , on a TEXT column ?
I am using replace function and trying to store the result into a varchar variable and the TEXT being truncated after 255 charecters.
for example, I tried this :
declare @str varchar(4000)
select @str = replace(cast(textcolumn as varchar(4000)), ' ', NULL)
I understand t-SQL dml statement(s)
will automatically truncate after 255 chars in a long string. But, seeking for other options here pls.
thanks for ur help..
Sir
How i m Manipulate String , I have
(ABC,XYZ,EFG) this string
know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get
like (ABCWE,XYZRT,EF)
pls help me out
Yaman
Hi
I have a field which is a file path
like
'C:avde8393948.txt'
I want to separate them into folder and filename
now the filename is always the same length,
so i can use RIGHT to get the filename,
but i prefer a method that from the right detects the
1st occurance of and everything after is the filename,
ALTER procedure [dbo].[sp_debit_credit]
as begin
SET NOCOUNT ON
DECLARE @DEBIT_ID INT
DECLARE @CREDIT_ID INT
DECLARE @servicedamount decimal(18,2)
declare @bal_Serviced_Amt decimal(18,2)
[Code] ....
table is like this
------------------------
DEBIT.ID, DEBIT.ACCOUNT_NO,DEBIT.SERVICED_AMT,CREDIT.ID,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,credit.Serviced_Amt
1456050866223983.0006418110.000300000.00
1456050866223983.0006419110.000500000.00
1456050866223983.0006447110.0002800000.00
1456050866223983.0006510110.0003100303.00
[Code] ....
I need result, I have to subtract
(DEBIT.SERVICED_amt - CREDIT.TRAN_AMT) and set CREDIT.SERVICED_FLAG =1 ,
if DEBIT.SERVICED_amt = CREDIT.TRAN_AMT then set debit.SERVICED_FLAG =1
Using with cursor. It will check row by row , i have written stored procedure , but flag is not updated .
Hi If i have a string like :
AX4030303022
in one field and need to split it into
AX 030303022
I can use LEFT(field_name,2) for the 1st one, but
what can i use for the 2nd ?
and how can i deal with NULLS ?
if its NULL i want a blank space..
Hello,
I have to perform some data manipulation based on ID1, ID2 with respect to Effective dates (below represented as effdt)
Source Code:
create table #source (id1 int, id2 int, effdt datetime)
insert into #source values (111, 123, '1/1/2007')
insert into #source values (111, 123, '3/1/2007')
insert into #source values (111, 123, '4/1/2007')
insert into #source values (111, 124, '5/1/2007')
insert into #source values (111, 125, '6/1/2007')
insert into #source values (111, 123, '7/1/2007')
Source Code Data:
ID1 ID2 Effdt
111 123 1/1/2007
111 123 3/1/2007
111 123 4/1/2007
111 124 5/1/2007
111 125 6/1/2007
111 123 7/1/2007
Target Data should look like:
ID1 ID2 Effdt Expdt
111 123 1/1/2007 4/30/2007
111 124 5/1/2007 5/31/2007
111 125 6/1/2007 6/30/2007
111 123 7/1/2007 7/31/2007
The logic is like the min(effdt) as effdt, min(effdt) - 1 as expdt of the next id2 from the source. Nut the problem comes when the same ID2 comes later after a different id2, so just a group by on id1, id2 does not suffice.
If it is the last record then the expdt is that month's end date.
Can someone throw me some light on what kind of logic I should be using to accomplish this.
Thanks in advance.
Greg
I would like to drop the leading 0x on a binary value so I can do abitwise operation.Here is simplified code:select right(0x88186000,8)I expected to get back 88186000, this was not the case. The commandreturned some wierd characters.Am I missing something?
View 3 Replies View RelatedWe have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid
View 3 Replies View RelatedHi all,
I am having problem in string manipulation in SSIS - Derived Column Transformation.
I am trying to extract the OU names from Active Directory objects into a SQL table.
Assume that a distinguish name (DN) of an object as below:
CN=John, Doe,OU=Users,OU=SubOU,OU=ParentOU,DC=domain,DC=company,DC=com
How can I manipulate the above string so that I get:
ParentOU/SubOU/Users
Thanks in advance. Help is much appreciated!
col1 col2 col3
1 IN NULL
2 FOR 1
3 Small 4
4 World 1
5 Hj 1
6 NJ 1
7 Welcome 1
8 So 4
9 We 1
10 Are 1
11 Resp 3
12 OIJIOJPOJPJO 7
13 OIJOIJ 1
14 LKJ 2
15 IJ 1
Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1 IN NULL
2 FOR 1
2nd set would look something like this
3 Small 4
4 World 1
5 Hj 1
6 NJ 1
7 Welcome 1
3rd set
8 So 4
9 We 1
10 Are 1
4th Set
12 OIJIOJPOJPJO 7
13 OIJOIJ 1
and the last set
14 LKJ 2
15 IJ 1
Any help is appreciated!!
Table1:
Id column1
--------------------
251 a
251 b
251 c
What I need to accomplish:
Table2:
Id column1 column2 column3
--------------------------------------------------------
251 a b c
Using SSIS i need to extract data from table1 and combine records with same id into one record in order to map to my destination table2
Hi
I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column
example
Each Scenario is grouped by ID
Scenario 1
columnA
Row1 X X
Row2 X
Row3 X
Row4 XYZ
Scenario 2
columnA
Row1 XA
Row2 XYZ
In both scenario's I need to control from front end
the number of row should remain 4
I am grouping by row field say Type
This field Type varies for each ID group
Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.
Thanks
I have a web page called homepage.aspx created in Visual Studio 2005 using VB. It contains some hyperlinks on the left side. I have a database created in SQL Server 2005. I need to add, delete, edit, update data in the database directly from this same page. How can I achieve this?. i.e is it possible to, say, have a form on a section of this same web page that would allow me to directly manipulate/modify and update the database? (If you may give code, you may use abstract names for the database, tables and column and/or field names-I will understand). Many thanks in advance.
View 3 Replies View Related