Finding Non Valid Dates With A Char Field

Aug 9, 2007

I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below

select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1

This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Any way around this?

View 2 Replies


ADVERTISEMENT

Finding Valid Identity Range On Merge Subscriber

Nov 22, 2006

I am using merge replication with automatic identity range management

I want to be able to give a disconnected subscriber a warning message if a certain percentage of the allocated subscriber identity range has been used
- so that they can make sure they reconnect soon to obtain a new identity range

I can get the current identity value OK, but I can't find a good way of finding the current permitted primary and secondary ranges

The information is available in the check constraint, but I don't really want to have to parse this!

CONSTRAINT [repl_identity_range_0DF907AC_40CF_4849_9BD0_2173C90A3805] CHECK NOT FOR REPLICATION (([ContactID]>(10000040000.)
AND [ContactID]<=(10000050000.) OR [ContactID]>(10000050000.) AND [ContactID]<=(10000060000.)))

I can't find any system view which will provide this information

Is there a recommended way of doing this?

Thanks

aero1

View 3 Replies View Related

Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat

Jul 23, 2005

Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn

View 2 Replies View Related

Convert Var Char Field To Date Field

Sep 27, 2005

Hi,

I need to convert a var char field to date field (DD/MM/YYYY)

Current convertion format:
CAST(Report_Date as DATETIME)

How can i convert to Date field with date format of DD/MM/YYYY?

Thanks.

Onn Onn

View 2 Replies View Related

Finding Dates

Mar 18, 2008

Any suggestions on how I would find the 2nd and 4th tuesday of every month?

View 5 Replies View Related

Finding Dates Within 15 Day Timespan

Sep 7, 2014

I have a table that contains activity dates. If a certain activity occurs 3 times within fifteen days, I need to flag them that account.What is the best approach?

View 1 Replies View Related

T-SQL (SS2K8) :: Finding Gaps In Dates

Mar 25, 2014

I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:

CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)

INSERT INTO #SessionTest
SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL
SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL
SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL
SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL
SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL

[code]....

View 6 Replies View Related

Finding Missing Dates For Each EmpIDs

Jun 6, 2008

Friends

I'm using Sql Server 2005, in which I've table like this

USE [Sample]
GO
/****** Object: Table [dbo].[Table1] Script Date: 06/07/2008 03:10:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpID] [int] NULL
) ON [PRIMARY]


INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)

My task is I want to find Missing Dates (Except Saturday, Sunday)
for each Employee.

Note: Missing Dates should be Working Days only (Excluding Saturday & Sunday)

Help me out

Thanks
Rajesh N.

View 6 Replies View Related

Excluding Weekends When Finding The (dd/hh/mm/ss) Between Two Dates

Mar 27, 2008

I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.



Any help would be greatly appreciated.



Thanks

JP

View 6 Replies View Related

Help With Dates-finding Number Of Days Per Month

Mar 28, 2000

I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.

A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.

Thanks in advance

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Differences Between Dates In A Single Colu

Feb 28, 2008

I have a log table that displays the history of the status of an entity and the date the status changed.

member_id | modification_date | status

I wish to find the differences between datetime values in different rows of the modification_date column. I would need the amount of time between whatever the next sequential time was for the member. I would also need to know the status change in that time. I don't know how to do this with just one date column.

Any assistance is appreciated.

Thank you.

View 2 Replies View Related

Pad Char(2) Field With Zero

Apr 6, 2006

Hi

I'm extracting a char(2) field from a table that has a value of '1' and writing it to another table but want the output field to be padded with a zero. I have tried this below:

right('00' + isnull(Field1, ' '),2

but the output field comes out as '1 ' (that's 1 followed by a space). Does anyone have any idea how I can do this or please point out what I'm doing wrong in my 'right(' fucntion above.
Thanks,
Jeff

View 3 Replies View Related

Converting An INT Field To CHAR

Oct 12, 1999

I have been asked to investigate the feasibility of converting an Invoice Number field from 6 to 7 digits, and allowing alpha characters. This means CHAR. The Invoice Number is not used outside of the application, but changes would have to be made to the database, and VB code that runs the app. Any ideas on how to proceed?

Any responses would be greatly appreciated!
Thanks

View 1 Replies View Related

Update Char Field...

Jul 12, 2007

ok, so we have a field in one of our db's, a do_not_synch field. this field has a datatype of char...

the following sql statement does not update the field..

update contacts
set do_not_synch = 'Y'
where id=1

and if i open up the table in sql server management studio, and cannot type in and save the value Y...yes i can update other fields in this row...so its not a permissions issue?

is there anything special i should have to do?

Cheers,
Justin

View 1 Replies View Related

DATEADD And Char Field

Apr 25, 2008

Hi,

I have a varchar field that contains values to represent the month and year in the format mmyy (eg. 0107, 0207, 0307 etc).

I want to be able to select the value that is 6 months before the current month and year, for example if I were running the query in the current month/year (April 08) I would return the value 1007 (October 07).

Is someone able to help me with this please?

Thanks

View 8 Replies View Related

Need An Unlimited Length Char Field

Dec 7, 1998

Hi,

If I want to make a field of characters to be unlimited length(or maybe 2k for example), what datatype should I use?
Char, varchar and text have a max. limit of 255...

Will appreciate any suggestions.

Thanks,
Nishi

View 4 Replies View Related

How To Replace Char In Ntext Field

Jun 16, 2006

I need help on replace char in ntext data type

Here is the example data
<qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off
20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree
</qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh

I want result look like this
First Column:The AE Understands what conditions the Account Manager is allowed to sign-off.
Second Column: Strongly Disagree Disagree Agree Strongly Agree

This is what i had so far
Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion

my result:
The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.

I have problem with replace '%20' and how to make the second column.
Any Help?
Thanks
Shan

View 1 Replies View Related

[rsInvalidExpressionDataType] The Value Expression Used In Field Returned A Data Type That Is Not Valid.

Jan 15, 2007

I got this error for a calculated field that calls a public function of a custom assembly based on the report parameter values. The return type is an array of double.

=code.ovalues.dGetValues(Parameters!Report_Start.Value, Parameters!Report_End.Value, Parameters!Tag.Value, Parameters!Agg.Value)

Does SSRS 2005 supports the type double for a field? Can the data source of a field in a dataset be an array? Is there a way to pass the array to a field by reference and display the values at runtime?

I would appreciate any help since I could not find anything helpful on the internet and Microsoft document!

View 14 Replies View Related

Char(4) Or Integer For Year / Quarter Field

Sep 3, 2007

Hello,

what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?

Both are part of a composite index.

Thanks
Silas

View 6 Replies View Related

JOINing On CHAR Fields Of Different Field Lengths

Nov 30, 2007

Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results?

I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate.

Any comments on this would be appreciated.



Rye Guy

View 3 Replies View Related

Search And Replace Only Replaces One Char Per Field

Sep 22, 2006

I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script

DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)

SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'

SELECT @patfind = '%' + @find + '%'

UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind

View 1 Replies View Related

Search And Replace Only Replaces One Char Per Field

Sep 26, 2006

I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script

DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)

SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'

SELECT @patfind = '%' + @find + '%'

UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind

View 4 Replies View Related

Varchar Vs Char In 1st Field Of Composite Clustered Index

Jul 23, 2005

Would it be OK to use varchar(5) instead of char(5) as the first field of acomposite clustered index?My gut tells me that varchar would be a bad idea, but I am not finding muchinformation on this topic on this when I Google it.Currently the field is Char(4), and there is a need to increase it to hold 5characters.TIA

View 2 Replies View Related

Extract Date Part Of Timestamp Column And Convert It To Char Field

Nov 28, 2012

Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?

The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.

convert(char(15), process_date,112) as process_dt

View 3 Replies View Related

What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?

Aug 29, 2007

Hi,
What is the difference updating a null value to char/varchar type column

versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?

Example:

Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?

View 9 Replies View Related

Help Finding Current Size Of A Field

Jan 19, 2006

How do I find the current size of each field in a table's column?

I have a table with a field for notes/memos. I need to see which ones are about to reach the size limit and what the current size is.

Does this make sense?

Thank you,
Karen

View 2 Replies View Related

Finding Out Field Names By Lineage ID

Apr 26, 2006

Hi,

is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable...

The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID...

Thanks,

View 1 Replies View Related

Finding And Grouping Records Off Of A Given Field Value

Jun 16, 2007

In the "tblEmailGroupLink" table...

I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.



In the "tblEmailAddress" table...

The same "Emailid" field has a corresponding "EmailAddress" field.



What needed is all the email addresses found in the "EmailAdddress" field of these records.





I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.



Thanks,

Bill

View 2 Replies View Related

Finding Unique Field Names With Different Values

Jun 8, 2005

I am in a situation where I need to find out unique field names with different values in a table having 200+ columns.
Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values.
Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values.
I would appreciate If anybody gives me hint about achieving desired result
 

View 2 Replies View Related

Date Between Two Dates And Sum Of A Second Field

Jun 25, 2012

I'm just getting into using sql server 2008 and the report builder 3.0 addon after a long time away from sql and I was wondering if the following was possible.I have a number of records with a start and end date along with a volume i.e.

Delivery Start; Delivery End; Volume
01/06/12; 05/06/12; 25,000
01/06/12; 02/06/12; 50,000
24/05/12; 26/10/12; 5,000

Essentially this means for each day between the two delivery dates the stated volume is delivered, what i need to try and do is to find the volume for each day between two user entered dates, giving a result along the lines of

Start Date Entered = 01/06/12
End Date Entered = 03/06/12

Date; Total Volume
01/06/12; 80,000
02/06/12; 80,000
03/06/12; 30,000

Is this actually doable?

View 4 Replies View Related

Seperating Dates By Field.

Mar 19, 2007

Hello, this is my first post to these forums so... Hi.

I'm currently doing a select statement and I cannot figure out how to get it to do what I want it to.

I'm trying get the statement to show how much activity was done in each year in a separate field and I don't have any idea where to start to get this accomplished.

Here's the code:




select distinct client.Number as [ClientNumber], client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip,
count(distinct [transaction].date) as VisitsWithinDateRange, client.LastVisit

from client

inner join [transaction] on client.number = [transaction].client

where

[transaction].date >= '01/01/1980'
and
[transaction].date <= '01/01/2008'
and
client.status like '%a%'
and
client.status not like '%n%'
and
client.status not like '%i%'
and
client.clienttype <> 'C'

group by client.number, client.Number, client.FirstName, client.LastName,
client.Address, client.City, client.State, client.Zip, client.LastVisit

View 3 Replies View Related







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