Creating A String From Date Fields
Jul 20, 2005
I have a table with a startdatetime and an enddatetime column such as:
StartDateTime EndDateTime what I want to see returned
is:
01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
1:30PM"
01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
10:30AM"
Maybe someone has accomplished this aready in a stored procedure and
has an example of how to do it?
lq
View 2 Replies
ADVERTISEMENT
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
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Aug 20, 2007
I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!
View 1 Replies
View Related
Aug 17, 2005
writes "Hi,
I am VERY new to SQL and I do not know if I am word this question correctly, so forgive me.
I would like to create a new table that does calculations on some of the same fields.
•IPA number (from the IPA_Num field)
•Total number of Rx's (sum of rx field )
•Total Paid( sum of amt_paid field)
•Total generic paid (If the field drugs = 1 then sum the amt_paid field)
•Percent generic (If the field drugs = 1 then sum and divide by the sum of the field rx)
•Percent formulary (if the formulary field = A then sum and divide by the rx field)
•Cost difference ( Average amount paid per brand Rx - average amount paid per generic Rx )
Any help is greatly appreciated.
Blessing to all
Yvonne"
View 5 Replies
View Related
Oct 23, 2014
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
View 6 Replies
View Related
Oct 13, 2013
I need creating an update trigger that involved two tables and a few fields.
tblCases
Fields
Defendent1
Defendent2
Defendant3
tblCaseBillingDetails
Fields
DefCount
I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.
View 1 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Apr 28, 2006
I have data looks like below
23-4c-4f-3rd-2
23-5c-4ad-3-2
THis is all in one field. I need to use sql to take out the data between the 1st and third hyphens to return
4c-3rd
5c-4ad
View 2 Replies
View Related
Sep 30, 2015
Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "
View 3 Replies
View Related
Mar 23, 2008
Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox sRCName refers to the field sRCName. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Thanks.
View 3 Replies
View Related
Jan 8, 2008
I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion.
Is there any way to do this so that i do not need to write an update query for every column?
Would appretiate an example if anyone knows how to manage this.
Thanks!
View 2 Replies
View Related
Jul 20, 2005
greetings!! the below trigger works fine in SQL 2000 and doesn't takeup much resources, it's a very simple solution to trim text fields atinput in SQL. I know a lot of folks will say to create input masks atthe UI level and that SQL is a restricted back-end DB, but to be quitehonest I don't trust those UI guys and as a DBA I will have to cleanthe mess when they forget to validate. Hope it can be usefull to anyof you too. ;)----------------------------------------------------------------------CREATE TRIGGER [TRIMMER_TGR] ON [dbo].[MyTable]AFTER INSERT, UPDATEASIF UPDATE (MyStringField)DECLARE @TRIMMEDFIELD NVARCHAR(50)DECLARE @MYID INTSELECT @TRIMMEDFIELD = MyStringField from InsertedSELECT @MYID = ID from InsertedBEGINUPDATE tblDocket SET MyStringField = RTRIM(LTRIM(@TRIMMEDFIELD))WHERE ID = @MYIDEND
View 6 Replies
View Related
Feb 15, 2008
Hi,
I Have this simple convertion in a Script component
Dts.Variables("dateOfProcess").Value = CDate(lineMCF.Substring(30, 2) + "/" + lineMCF.Substring(28, 2) + "/" + lineMCF.Substring(24, 4))
this works fin in my development environment which has a spanish version of SQL Server and uses "DD/MM/YYYY" as date format.
but the production environment has an english version of SQL Server and "MM/DD/YYYY" date format, so the package crashes in this server.
How do I convert the string to date not depending on the SQL server language.
thanks.
View 2 Replies
View Related
May 26, 2006
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
View 1 Replies
View Related
Apr 23, 2014
I have a field that will look something like this. I need to read a file and when the first 3 positions are REM, the number following is the Order number, and the initials, are the rep. this is how they are tracking for the rep, if they sent reminders to customers and when.
REM 0482348 LV 04/13
I would like to do this in SQL and also join to the order header file that will give me the invoice total for that order. what is the best approach here via SQL for these first 2 fields' needed?
View 1 Replies
View Related
Sep 20, 2007
Hi
I need to export data to CSV file from Sql Reporting Services.
I am including single quotations in my view to display in SRS for string fields.
After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Please let me know if anybody knows.
Thx
Vijji
View 4 Replies
View Related
Feb 6, 2007
I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.
Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2
What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Is this something that can be done with grouping and sorting?
Thanks,
Chad
View 1 Replies
View Related
Jan 8, 2013
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
View 14 Replies
View Related
Jun 21, 2006
Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
View 2 Replies
View Related
Mar 11, 2006
Hello.
I need an SQL Statement for SQL Server in VB.Net that will return values from a table for parts that are due for maintenance within 1 month.
Select * from Part Where DateNOverhaul <= '" & DateAdd(mm,-1,Date) & "' is not working.
I have been trying various solutions on the web and am getting confused because none seem to work. Please help me.
Thank you.
View 7 Replies
View Related
Jul 23, 2005
Hello !How to save on SQL server a date in this format?dd-mmm-yyyyAlso, I have a web form in ASP.net and I want the users to enter datesonly in the above format. I am going to need to make calculationsbeased on the above.Many Thanks,Marios Koumides
View 2 Replies
View Related
Dec 18, 2007
Im creating a table that links employees to jobs, each employee can only be in one place at once. I have fields for start date and end date. Whats the best way of ensuring an employee cannot be assigned to more than one job at the same time.
Im thinking update/insert trigger that ensure Start Date or End Date being added is NOT >/=existing Start Date and </=existing End Date.
Anyone any better ideas?
Thanks in advance
Alex
View 3 Replies
View Related
Feb 23, 2015
I have a SQL select syntax as below
0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
View 7 Replies
View Related
Jul 14, 2006
I have a trigger that writes changes to an audit table. In the case of Updates, I only want to write out the fields that have actually changed. My code for COLUMNS_UPDATED evaluates to true for all varchar fields, even when they haven't changed. All other scenarios appear to be working correctly.
WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF ((@TriggerType = 'I') OR (@TriggerType = 'D') OR (@TriggerType = 'U' AND substring (COLUMNS_UPDATED(),@char, 1) & @bit > 0))
BEGIN..........
END
END
Ideas? Thanks!
View 1 Replies
View Related
Mar 5, 2008
We have a mobile device project, which has a database file (Database.sdf) as part of the project.
I am trying to connect to it to insert a record. The syntax I'm using is:
string connection = @"data source=Database.sdf";
SqlCeConnection conn=new SqlCeConnection(connection);
conn.Open();
I have tried various connection strings such as
"data source=MyDocumentsDatabase.sdf";
and
"data source=C:MyProjectsThisParticularProjectDatabase.sdf"; << the connection string in teh server explorer
and
"data source=MyAssemblyName.Database.sdf";
and
"data source=MyAssemblyNameDatabase.sdf";
I've also tried getting the path the the executing assembly and adding that in, but still no luck.
but they all throw the error:
The database file cannot be found. Check the path to the database. [ File name = Database.sdf ]
I have searched the internet and found no explanation about how the path to the Database is made up (ie is it the path on *my* computer, or the virtual mobile device?) and how I can actually get the path the the database without hardcoding it.
Can anyone help please??
Thanks!!
View 6 Replies
View Related
Nov 29, 2006
I am trying to order two date columns. One Ascending theother Descending. I am using the Orders table of Northwind in SQLServer. No idea why, but it only sorts on the first column I setcriteria on. I think you have to convert it to date as it gets storedas date/time...I triedSELECT TOP 100 PERCENT OrderID, CustomerID, EmployeeID, OrderDate,CONVERT(char(10), RequiredDate, 103) AS RequiredDate,CONVERT(char(10), ShippedDate, 103) AS ShippedDate, ShipVia,Freight, ShipName, ShipAddress, ShipCity, ShipRegionFROM dbo.OrdersORDER BY RequiredDate, ShippedDate DESCBut odd results occuredAny ideas appreciated
View 3 Replies
View Related
Feb 11, 2008
i have two columns in sql which is datetime datatype
fromdate & todate, now i want to display it like fromdate-todate in my front end
select fromdate +'-'+ todate as date from table.
when i use this query , the error is
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Thanks for any help
View 1 Replies
View Related
Dec 27, 2004
hi,
When the autogenerate property of datagrid is set to true, how to perform sorting on date fields.
Example:
SELECT DC_NO AS [Document No],CONVERT(CHAR(10),DC_DT,103) AS [Document Date],
[Name] FROM XYZ
Here we convert the date field to the format "dd/mm/yyyy" so when sorting is done it is done in the format "dd/mm/yyyy". But instead sorting should be done in "yyyy/mm/dd" format.
View 6 Replies
View Related
