Check Data Validity (formats)

Jul 11, 2002

l have a customer text file.l have imported the file into an sql table. Now l want to validate the data starting with the customer_no and the format being xxl-0001 etc ,phone_no i.e 011 as the code and the number as 9028589 etc , say balance

l want to have ensure that the format of customer_no is XXL- then the number
Check the phone number that it has the right number of characters and that it is an interger.

How ould l do this? Should l use a procedure to do this or triggers? How would the code look ? Is there a topic on data validation that can give me pointers? Please assist

View 2 Replies

Importing Data From Various Data Sources With Non Standard Formats

Mar 19, 2007

Hi all :)

I'm wondering if SSIS will be the solution to the problem I'm working on.

Some of our customers give us an Excel sheet with data they want to insert or update in the database.

I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.

This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.

But now I��ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.

Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I��ve looked at programmatically creating the package but I��ve got to say that��s quit hard to do�� It would be easier to write the whole thing myself than to create the package trough code ;)

If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.

So how should I solve this problem? Use SSIS or not?

Thnx :)

Wouter de Kort

View 6 Replies View Related

How To Verify The Validity Of A File Prior To Attempting Restore?

Mar 22, 2007

Hello,

I would like a way to check a file before attempting to restore it. I want to be able to tell that this is a valid SQL backup file and not just some random file before I attempt a restore.

Any help would be appreciated!

hassiah

View 5 Replies View Related

Transact SQL :: Changing Date Formats When Importing Data

Aug 9, 2015

I’m retrieving Yahoo quotes into my database and have run into an issue when the dates sometimes change format in the csv file retrieved.

I am retrieving yahoo quotes via powershell, then running a package to import data to my table. This generally works expect when the yahoo date format changes.

In the yahoo csv file, the dates normally come through in dd/mm/yyyy format. I find when a quote is old the format changes to mm/dd/yyyy, just for that particular quote.

When this happens, the package fails because the quote date format does not match my destination table format. i.e. mm/dd/yyyy vs dd/mm/yyyy

When this occurs, I would like to skip the records in mm/dd/yyyy format altogether and have the rest of the quotes imported.

One approach I can think of is to import the dates as a text type and do some data validation / conversion once imported but it feels like adding unnecessary steps.

Is there some other way I can achieve this within the process I already have?

View 4 Replies View Related

Data Access :: How To Check All Connection Automatically During Routine Check By Using Batch File

May 20, 2015

I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.

View 5 Replies View Related

Date Formats...UK

Oct 9, 2001

My SQL 7.0 database is set to show UK format date ranges. But when opening a table and adding criteria to the grid pane I having to put the US format date to retrive correct results...

Is this correct. I am wanting to be aboe to enter UK format in the criteria

Thanks...Scott

View 2 Replies View Related

Date Formats In SQL 7

Jan 23, 2001

Hi,

I am creating a VB application that will run on a NT machine with Regional Settings as UK English and dd/mm/yyyy settings. When I try to input these values into SQL 7.0 ( default database setting mdy and us_english with Regional Settings as UK dd/mm/yyyy) , I get the error -" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value " .
Can I change my databse to always be dmy format. It seems to be active for a connection , can it be configured somewhere .

Any help will be greatly appreciated !

Regards

Anand

View 3 Replies View Related

Changing Formats

Apr 25, 2007

Hey guys,
I have a file that has date formatted like so: 2006-11-16 20:12:00
I would like the dateformat to be like mm/dd/yyy hh:mm

The file is being pulled into a varchar field..... as 2006-11-16 20:12:00
when I do a conversion i can only get it to mon 11,2006

any suggestions

View 1 Replies View Related

Date Formats

Dec 4, 2006

I read this and got confused...I use PHP and got this:$sql = "delete from blabla where startdate< =to_date('2000.05.05','YYYY-MM-DD')";$result=odbc_exec($conn, $sql);Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not arecognized function name.to_date should work?At the other end i'd like to read out startdate as a date var or formatit as I need.... (dd.mm.yyyy). Convert makes me confused... helpplease?S

View 3 Replies View Related

Q. About Binary Formats.

Jul 20, 2005

Is it true that binary and varbinary has a max-kapacity at 8000 bytes? Imjyst unsure how to read the dokumentation and want to be completely sure.And image is only one to use for data aobve that size?Thx in regards for clearing things out for meReagardsAnders

View 1 Replies View Related

SSRS Formats

May 1, 2008

Hi,

I have two beginner questions:
1. How should I set up report layout/printing format so it would fit to the page?
2. Is there a way to change the color of every other row in the table? Can I highlight the rows based on some kind of a condition?
Thank you.

View 4 Replies View Related

Time Formats

Mar 7, 2007

OK - totally new to this game but here goes ...

I want to put together a database to record times achieved by swimmers in competition. The database itself is simple but I don't know what format to use to store the time achieved. Times can vary, for example 35.27 (SS.00), 1.12.63 (MM.SS.00). Oh, and by the way I want to rank the output but haven't even begun to think about that yet!

View 3 Replies View Related

SQE Vs. SQL 2005 .mdf Formats?

Sep 4, 2006

Hi,

My ISP says that the file format for SQE .mdf files is different than a regular sql2005 .mdf file. So, what I am told to do is backup my SQE file, and send it to them to restore into a SQL2005 format.

Does that make sense? Or should I simply be able to upload a .mdf file to their SQL2005 server without backing it up for them to restore?

Thanks,

-JS

View 6 Replies View Related

Different Datetime Formats

Sep 25, 2006

Hello..

I have a problem with DateTime...In my local sql server I use "mm.dd.yyyy" format and i work fine with the database. But that crashed in my clients sql server. Their sql server wants "dd.mm.yyyy" format. Ofcourse I can use parameterized queries and solve the problem but I write the program on WinCE5.0 and I use the RDA method to access the SQL Server.In RDA,I have no chance to use parameterized queries as I know.

How can I solve the problem and what causes this problem? A setting in sql server or something else?What causes this difference..?

I wish I could explained my problem..

Thanks in advance.

View 4 Replies View Related

Calculations And Formats In SRS

Nov 2, 2007

Hi--

I am relatively new to SQL Reporting Services and have a couple basic questions (I think they are basic).

First, lets say that I have the following fields in the body of my report: Sales, Cost, Profit, and Percent Profit. Percent Profit is a calculated field of profit/sales.

Then, I have a grouping by customer, with a footer with the sum of sales, sum of cost, sum of profit. And I also want percent profit, but I do not want a sum or average of percent profit. Instead I want percent profit calculated as sum of profit/sum of sales. I cannot seem to get this to work with a calculated field. I must have the syntax wrong (as I was just taking sum(fields!profit.value)/sum(fields!sales.value)*100.

Also, I understand the format of #,# can be used to print a blank instead of zero. But this format also suppresses any decimals. So if I have a field that needs two decimals when there is a value, but need it to be blank when 0.00 is returned, what is the appropriate format?

Any help that can be provided would be much appreciated. As books are great, but sometimes do not explain everything. Thanks!
-Christina

View 8 Replies View Related

Time Formats

Mar 7, 2007

View 5 Replies View Related

Using A Cursor To Check Data

Feb 1, 2008

Hi, here is my problem and hopefully somebody has an idea. What i am working on is an order tracking system. i insert the orders into sql then when the orders come in i check for the oldest order and apply the qty we recieved to that order...pretty simple. The problem is that some times we have 5 orders for the week and we then recieve them as one order so there will be 5 orders to fill. I can do this for 2 orders and was thinking about using a cursor to so this instead of a really drawn out procedure but am not sure how that would work if it would at all. Does anybody have any ideas?

View 1 Replies View Related

How To Check Data In A Certain Location

Oct 14, 2004

Hi all,

I need to compare and match two fields, the data can be in following manner:

first_name/tableA first_name/tableB

Diana R Diana

Diana Diana Rosa

Diana R Diana R

Diana Patrick

My result should find only Diana and Patrick as nonmatch and all other as match.

How should I write the query?

Thanks in advance!!

View 1 Replies View Related

Check Data Format

Jun 18, 2001

1)How would l write sintax to check data format in sql? Eg field customerNo should be 10 characters long and alpha numeric. Date should be etc.

2) How do l reference a value in another cursor?

View 1 Replies View Related

To Check Range Of Data

Mar 23, 2006

Hi Guys,
Need your help on this problem.
Let say table name call tbl_range and 2 field call No1 and No2
I have this set of record :-
No1 No2
1000 2000
2001 3000
5000 6000

My problem, i want to check if user insert another set no No1 : 1500 No2 : 2500. So, this means that range already clash with another range. If this happened it will return 2 record (1000 - 2000) and (2001 - 3000). Can it be done and how?

Regards,
Shaffiq

View 4 Replies View Related

Check Data Before Update

Jul 20, 2005

Hello Everybody!I have a POLINE table on a SQL Server 2000 DB. Before I update therecord I need to check that either field, STORELOC or WONUM has dataon it. If both fields are NULL I would like to send a message lettingthe user know that either fields needs data before they can save therecord. If any of the fields have data then, it is OK to save therecord.Could you please let me know how to accomplish this? An example willbe really helpful, I can do this in Access but I do not know how to doit in SQLServer. I was thinking using trigger but there are not reallygood examples.Thanks in Advance!Martin

View 1 Replies View Related

Data Validation Check

Jan 31, 2008

I new here so hopefully I'm asking this question in the correct forum. I'm have a flat file that contains numbers that I need to verify that they begin with certain prefixes so they load to the correct client. For example in the flat file if I'm loading data for client A and their account numbers begin with 045XXXXXXX then it loads the data. But if there is a record that begins with 037XXXXXXX it should be loaded to client B instead and that records gets written to a error file.

So to summarize what I need , I'm looking for a check to kick out records if I'm loading client A's data versus if I'm loading client B's data.

Can this be done in a Conditional Split ?

Shanon

View 9 Replies View Related

Check Data Changes In Table

Oct 27, 2007

Hi all!
I need to check data changes in some tables from specified date. Can it be done without triggers for each table?
SQL Server Management Studio always says "Data was changed" if another user updates data and you try to update old version. How it checks data modification date? I found only this:

USE [ScheduleDB]

GO

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TTest]') AND type in ('U')

but it can only show structure modification date.

View 3 Replies View Related

Check Data Before Insert

Aug 15, 2007

I need help thinking about this problem :-)

I have an SSIS pkg that automatically downloads financial extracts from an ftp site. Once the files are downloaded, I load the extract to a table. The table is first deleted before the insert, so that each time the table has "fresh" data (whatever was in the extract for that day).

Once the extract data is in the table, I load the data into yet another table that combines data from many tables. Simple enough.

Problem is, sometimes when I download the extract, it hasn't been updated yet, so I'm downloading an OLD extract. This old data then gets loaded into the first table. That's ok, because it doesn't really hurt anything. I can always delete the table and reload it if necessary.

The problem occurs when the old data goes from this table into the OTHER table. We don't want old data in this other table!

I need a way to check that I'm not loading the same data 2 days in a row into the OTHER table.

I realize that I might be able to solve this problem without using SSIS, but the solutions I've come up with so far aren't 100% satisfactory. I can use a query to check dates and that sort of thing, but it isn't foolproof, and would create problems if I need to manually force the process though, that is, if I need to override the date logic.

Anyways, I'm wondering if there's an SSIS approach to this problem... I can't rely on timestamps on the data files either. They're not accurate.

This is has been very perplexing to me.

Thanks

View 8 Replies View Related

Converting Date Formats

Apr 10, 2000

Hi,

I need to convert the output of a query

From:
Sep 13 1999 12:00AM

To:

1999-09-13 00:00:00.000

I need it to be in 7.0 format)

Thanks.

View 1 Replies View Related

Good Ol` Date Formats And BCP

Oct 26, 1998

Hey all,
I`m trying to BCP in some data from a csv file. It has dates formatted like d/mm/yy and when run BCP it errors. Of course...SQL language is set to English(United States) so it wants it in mm/dd/yy. But from what I`ve read in the archive, from Sharon at least, is that only in BCP will it use the NT date setting which for me is English(Australian) or d/mm/yy. So I`m thinking, `that`s ok` my data`s dates like d/mm/yy and nt dates like d/mm/yy it`ll bcp in fine - but no.

What can I do? I cant change my sql setting and I cant change the way I dump my dates to the csv! Suggestions......

TY
Simon

View 2 Replies View Related

Conversion Between Date Formats

Jul 23, 2005

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when wewant to display this date via a web frontend, it needs to be indd/mm/yyyy. I've declared a function (shown below) which convertsbetween these date formats and returns a varchar(20). This works finehowever now I need to have the ability to sort on this date field inthe frontend. This requires my function to return a datetime in therequired format. Can this be done?DECLARE @InputDate nvarchar(20)DECLARE @OutputDate nvarchar(20)DECLARE @Day nvarchar(2)DECLARE @Month nvarchar(2)DECLARE @Year nvarchar(4)DECLARE @Time nvarchar(12)SET @InputDate = '2005/03/01 14:30:00'SET @Day = cast(datepart(day,@InputDate) as nvarchar(2))SET @Month = cast(datepart(month,@InputDate) as nvarchar(2))SET @Year = cast(datepart(year,@InputDate) as nvarchar(4))SET @Time = substring(cast(@InputDate as nvarchar(23)),12,12)SET @OutputDate = replicate('0',2-len(@Day)) + @Day + '/' +replicate('0',2-len(@Month)) + @Month + '/' +@Year + ' ' + @TimeSELECT @OutputDate AS OutputDateThxVilen

View 1 Replies View Related

Outher Joins Formats

Jul 20, 2005

HiI work ith sql server 2000 and i need know the diferentof joins in format not ansi ( with * ) and joins in formatansi ( with 'outher join on' ).Two format work equal ???What is de correct format ???Thank you.R.

View 2 Replies View Related

Changind Date Formats

Jul 20, 2005

Hi.I had a VB program that was using an SQL Server 6.5 DB. Date formatwas dd/mm/yyyy. Now the DB was changed to an SQL Server 7 and the dateformat is yyyy/mm/dd.How can I do for changing date formats in the new DB to the oldformat?I�m searching for a database level solution, not server level.thanks

View 1 Replies View Related

Character Field Formats

Jul 20, 2005

I was wondering what everyone felt about the fomats in characterfields where the front end application accepts anything.I wouldn't want a customer table where the customer name was lowercase on one, upper on another and who knows on the third.If character fields are not consistent, then formatting will have tohappen every time someone access the data for reporting - as anexample ...Thanks,Craig

View 1 Replies View Related

Dreaded SQL Date Formats (UK/US)

Jan 18, 2008

Hi Folks

I have a SQL date problem that I just cant get to the bottom of.
A SSIS package runs that inserts dates into a SQL table from strings in the format dd/mm/yyyy
This package is run from within a SQL job.
Unfortunately the dates are being interpreted as mm/dd/yyyy. I have checked every possible date setting that I can, but nothing seems to work (SQL Agent account lang is set to British English, collations, Locale ID setting in the SSIS package, Lang of account that runs the SSIS service).

When the package is run in BIDS it inserts the dates correctly, just not from a SQL job.
I have tried inserting SET DATEFORMAT dmy in an Execute SQL Task in the SSIS package but that does not work either.

The baffling thing is that it works on one server but not another. Both servers have the same collation, regional settings and use the same SQL Agent account.

Also when SELECT @@Language is used is this determined by the lang setting against the user ?

Any help would be appreciated. Thanks in advance.

View 3 Replies View Related

Date Formats In SSIS

Dec 1, 2006

Hi once again guys,

I seem to be struggling with everything in SSIS these days!

I have a datetime field and I want to convert it to the following format in my derived column component :

yyyy.mm.dd

I also have another datetime field but this time I am only interested in the time values and I want to get :

HH:MM

How do I go about doing this in the SSIS expression builder?

Please help.

View 14 Replies View Related

Send Report With 2 Or More Formats

Dec 14, 2007

Is there a way to send a report with 2 or more formats? Let's say a customer wants a report in both PDF and Excel format in the same email. Is this possible?

Thanks,
Stuart Fox

View 3 Replies View Related