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


ADVERTISEMENT

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

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

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

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

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

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

Changing Date Formats

Dec 23, 2005

Hello. I am using Microsoft SQL Server Management Studio (SQL Server 2005). When I select a date column from a table, the date is displayed in "mm/dd/yyyy hh:mm:ss" format. Is there a way i can change this date format so that it shows "dd/mm/yyyy hh:mm:ss" permanently? Thanks.

View 3 Replies View Related

Server Configuration For Date Formats

Jan 16, 2004

Hi,

I'm using 2 DB servers, with MS SQL Server 2000 on each of them, one for development and one for production.

When I want to query some information, filtered on a date in the WHERE clause, I have to use DATE = '<MM/DD/YYYY>' on the development machine, and DATE = '<DD/MM/YYYY>' on the production one, to get the result I want, and I can't figure out why (I'm just a lambda user). I would like to configure the servers so that both use the <DD/MM/YYYY> format.

Could somebody help me on this case ?

Thanks in advance...

View 2 Replies View Related

Select Statement With Date Formats

May 7, 2008

select ID,MODIFIED_DT from sample WHERE convert(varchar,MODIFIED_DT,111) like '%2008/04/28%'


My output is
id modifieddate
8 2008-04-28 08:24:10.000
4 2008-04-28 08:25:53.000
7 2008-04-28 08:28:33.000
8 2008-04-28 08:42:25.000

now my query is like this


select ID,MODIFIED_DT from sample WHERE ID = 8 OR ID = 7 or = 6 or ID = 5 or ID = 4 and convert(varchar,MODIFIED_DT,111) like '%2008/04/28%'

my output is like this
id modifieddate
8 2008-04-28 08:24:10.000
4 2008-04-28 08:25:53.000
7 2008-04-28 08:28:33.000
8 2008-04-28 08:42:25.000
5 2008-04-29 09:41:01.000
5 2008-04-29 16:34:52.000
7 2008-04-29 16:47:20.000
8 2008-04-30 10:11:02.000




Why do the values with date 2008-04-29 and 30 are coming .

View 3 Replies View Related

How Do You Change Date Formats Using Design Table

Aug 2, 2004

I'm looking to change the format of the date in one of my columns, how do you do that?

And what is the default date? Is Time included in that?

View 1 Replies View Related

How To Change Date Formats In Stored Procedure

Oct 4, 2005

I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.

thanks
mike

View 14 Replies View Related

Date Formats From OLE DB Source (SQL) To Flat File Destination

Oct 23, 2007



I am bring a date from a OLE DB Source (SQL Command) as [select cast(convert(varchar,getdate(),101) as varchar(10))] to a Flat File Destination (CSV File). The data in the source is show as "1/1/2007", which is how I need it to display in the file. The flat file defaults to showing the data as "1/1/2007 00:00:00." I did change the destination field to a String, and still, I geting the timestamp. I tried using a data conversion transformation, and I am getting bargage data when converting the date to a string.

Can any one give me insight on how to populate a date into a comma delimeted file as "1/1/2007", not "1/1/2007 00:00:00."


Thanks in advanced.

View 8 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

Integration Services :: Errors Converting Date Time Formats With SSIS

Jul 10, 2015

I am getting below errors when I try to import data from csv format to a sqlserver table.The csv file has date column that has date with format: 7/10/2015  1:18:39 PM and the sql server is using datetime not null for that field in the table.

[OLE DB Destination [90]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".

[OLE DB Destination [90]] Error: There was an error with input column "Date" (138) on input "OLE DB Destination Input" (103). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

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

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



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 5 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

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

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

Disabling Export Formats

Apr 2, 2008



I'm currently using SQL Server 2000 and SSRS 2000 with the latest Service Packs.


I need to disable Excel Exports for a single report.

I've found a way to disable Exporting Formats but it disables for ALL REPORTS on the server.
This involves changing the rsreportserver.config file.
http://blogs.digineer.com/blogs/jasons/archive/2006/05/10/93.aspx


Another site mentions a way to disable Exporting Formats for a single Report.
http://mikemason.ca/2007/04/30/



Code Snippetusing System.Reflection;
using Microsoft.Reporting.WebForms;
using Microsoft.SqlServer.ReportingServices2005.Execution;

namespace MyProject
{
public class ServerReportDecorator
{
private readonly ServerReport serverReport;

public ServerReportDecorator(ReportViewer reportViewer)
{
this.serverReport = reportViewer.ServerReport;
}

public ServerReportDecorator(ServerReport serverReport)
{
this.serverReport = serverReport;
}

public void DisableUnwantedExportFormats()
{
foreach(RenderingExtension extension in serverReport.ListRenderingExtensions())
{
if(extension.Name == "XML" || extension.Name == "IMAGE"
|| extension.Name == "MHTML")
ReflectivelySetVisibilityFalse(extension);
}
}

private void ReflectivelySetVisibilityFalse(RenderingExtension extension)
{
FieldInfo info = extension.GetType().GetField("m_serverExtension",
BindingFlags.NonPublic
| BindingFlags.Instance);
if (info != null)
{
Extension rsExtension = info.GetValue(extension) as Extension;
if(rsExtension != null)
{
rsExtension.Visible = false;
}
}
}
}
}



I'm looking for further clarification of this process.
1. Where I can download the Microsoft.SqlServer.ReportingServices2000 dll?
2. Does anyone have a project example of the process? How/When to actually use the code quoted?

View 2 Replies View Related

Converting Datetime Formats And Layouts?

Apr 30, 2007

Opening DimTime table of AdventureWorksDW sample database in MS SQL Server Management Studio shows me values in FullDateAlternatKey like

View 3 Replies View Related

Problem With Money And Datetime Formats

Sep 29, 2005

I am getting the result in the format shown below but i want the Min Salary and Max Salary to be just 32000 and 40000 respectively. And can change my JobDate to normal 26/09/05 format. I know that convert(char(12), column name , 103) has to be used but its not working out for my sql code.Thanks in Advance @list nvarchar(200), @minsal money, @lbtime int, @Sortby int, @a intSELECT J.* FROM JobDetails AS J  JOIN (  SELECT DISTINCT J1.JobID  FROM JobDetails AS J1  inner join dbo.Split1(@list, ' ') AS S  ON J1.JobPosition LIKE '%' + S.Data + '%' AND (@lbtime = '0' OR MinSalary <= @sal AND @sal<= Maxsalary) AND (@a IS NULL  OR (J1.JobDate BETWEEN DATEADD(ww,-@a-1,getdate()) AND GETDATE() ) ) ) J1 ON J1.JobID = J.JobID ORDER BY CASE WHEN @SortOptions2=1 then JobPosition End,CASE WHEN @SortOptions2=3 then MinSalary END,CASE WHEN @SortOptions2=4 then JobDate END DESCJobID       JobPostion            Min Salary          Max Salary               JobDate6         SAP HR Manager       32000.0000          40000.0000          2005-09-26 00:00:00.000 

View 4 Replies View Related

How To Convert Hexadecimal File Formats?

May 19, 2000

Dear SQL folks,

We have files from an event log in a DEC Alpha server and we would like to create a database program to import these files and then be able to read and query the info in them.

These files contain information such as error codes about the machine that the Alpha controls. They also contain time stamps. I am told that these files are hexidecimal.

Someone mentioned in passing that if we use MSaccess as the database, we would first have to write a program to convert these files to a format that Access can understand.
But, we were told that SQL7 has Add-ins that comes on the CD that can convert these files to a format that SQL7 can then understand.

Does anyone know if this is true..and how difficult is file conversion to accomplish?

If anyone thinks that they might be able to help us, I can sent them a small sample of the hex files
Thank you in advance

View 1 Replies View Related

Import From A Flatfile With Rows That Have Different Formats

Jan 23, 2008

Hello Folks,

Im trying to use Integrations services to import a file on a daily basis.

My flatfile has to different type of rows that comes from an transaction system, it looks like this:

132,1/1/2008,00,123654,text,1,123.00
132,1/1/2008,00,123652,text,1,23.00
132,1/1/2008,00,123655,text,1,3.00
123,1/1/1/2008,01,149.00
1125,1/1/2008,00,123654,text,1,123.00
1125,1/1/2008,00,123652,text,1,23.00
1125,1/1/2008,00,123655,text,1,3.00
1125,1/1/1/2008,01,149.00;Cash;EUR;01;12

After the date you can see that there is two digits number either 00 or 01. The rows also have a different lengthts.

When ever that columns contains 00 the line should be inserted to a special text file, if the columns contains 01 it should to another file.

How can I solve this in a good way?

One of the problems I have is that when I try to import the rows the flat file connections indicates(erros message) that I have partial row in the file which is true since the the rows with the columns content 01 have more fields then the other.

Thanks for you help.

holtis

View 3 Replies View Related







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