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

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

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

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

Update Fields With Searched First Date Record Fields

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

Find The Newest Date Of 2 Date Fields

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

Creating New Fields Based On Several Calculations

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

UPDATE Statement Creating NULL Fields?

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

Creating Update Trigger That Involve Two Tables And Few Fields?

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

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

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

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

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

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

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

Import Flat Text File String Date To Database Date

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

Finding Fields In String

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

SQL Server 2008 :: Creating Index - Add Unique Key That Will Cover 3 Fields

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

Help Needed : Not Able To See Fields Value When Creating A Report By Calling A Stored Procedure

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

Search And Update All Fields That Contains String

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

SQL Trigger For Trimming String Fields

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

Convert String To Date Independent Of Date Format

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

How To Search For Fields Containing A Specific Text String

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

Read Parts Of Unformatted Fields String

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

CSV Export With Single Quotations For String Fields

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

Grouping Sorting String And Numerical Fields

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

How To Convert Date String To Date Time

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

Help With SQL Statement To Find First And Last Populated Field In String Of Fields

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

Fields Within A Certain Date

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

SQL Date Fields ASP.NET

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

Date Fields

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

SQL Server 2008 :: Remove Double Quote In String Fields

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

Update Trigger: COLUMNS_UPDATED Evaluates To True For All String Fields

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

Creating Connection String

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

How Do You Sort On Two Date Fields ?

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

Merging Date Fields In Sql

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

Sorting Of Date Fields

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







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