Detecting the INSERT format for DATE database fields?

Assume I got a database resp. table defintion with a DATE field. How do I fi
nd out in which format the
field should be filled in an INSERT SQL statement?
Sometimes when I enter e.g.
INSERT INTO ... VALUES (...., '2005-01-25',....);
or
INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
I got an error like:
ORA-01861: literal does not match format string
before I realized that
INSERT INTO ... VALUES (...., '25-01-2005',...);
is the correct format.
TomThomas Jerkins (tomjerk@.hotmail.com) wrote:
: Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
: field should be filled in an INSERT SQL statement?
: Sometimes when I enter e.g.
: INSERT INTO ... VALUES (...., '2005-01-25',....);
: or
: INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
: I got an error like:
: ORA-01861: literal does not match format string
: before I realized that
: INSERT INTO ... VALUES (...., '25-01-2005',...);
: is the correct format.
You asked in an oracle group, so I'll give the oracle answer.
INSERT INTO ...
VALUES (...., to_date('25-01-2005','DD-MM-YYYY') ,...);
look up to_date for all the options.
This space not for rent.|||Thomas Jerkins wrote:
> Assume I got a database resp. table defintion with a DATE field. How
> do I find out in which format the field should be filled in an INSERT
> SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
The only guaranteed formats inserting a date are the following:
select convert(datetime, 'YYYY-MM-DDThh:mm:ss.mmm')
select convert(datetime, 'YYYYMMDD hh:mm:ss.mmm')
All others are subject the locale and risk throwing exceptions or
inserting the incorrect date.
David Gugick
Imceda Software
www.imceda.com|||"Thomas Jerkins" <tomjerk@.hotmail.com> a crit dans le message de
news:cst3bg$osr$05$1@.news.t-online.com...
| Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format
the
| field should be filled in an INSERT SQL statement?
|
| Sometimes when I enter e.g.
|
| INSERT INTO ... VALUES (...., '2005-01-25',....);
| or
| INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
|
| I got an error like:
|
| ORA-01861: literal does not match format string
|
| before I realized that
|
| INSERT INTO ... VALUES (...., '25-01-2005',...);
|
| is the correct format.
|
| Tom
|
Never rely on implicit conversion.
Use to_date to explicitly convert your external format to date datatype:
insert into ... values (..., to_date('2005-01-25','YYYY-MM-DD'),...);
or
insert into ... values (..., to_date('2005-01-25-13.14.15.00','YYYY-MM-DD-HH
24.MI.SS.FF2'),...);
or whatever is your external format.
This is the correct way.
Regards
Michel Cadot|||On Sat, 22 Jan 2005 09:37:04 +0100, tomjerk@.hotmail.com (Thomas
Jerkins) wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
>Sometimes when I enter e.g.
>INSERT INTO ... VALUES (...., '2005-01-25',....);
>or
>INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
>I got an error like:
>ORA-01861: literal does not match format string
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
>Tom
You should never rely on the default date format, and always apply the
to_date function to any date literal submitted.
The default date format, btw, is in the docs (no one ever reads them)
and can be queried from nls_session_parameters.
But you should stop developing bad habits and not rely on it. It can
be changed on client level very easily, in which case your application
won't work anymore.
Sybrand Bakker, Senior Oracle DBA|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
Please don't cross post Oracle problems in other newsgroups. But if you were
using DB2, it would take the date in either order.|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
> find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
you can check the setting of NLS_DATE_FORMAT in the V$NLS_PARAMETERS view
however, you should not rely on the default format because it can be changed
by the dba or by other code that you run
1) if using 10g, use the DATE keyword to specify an ansi date literal, ie
DATE '2005-02-22' (no time element)
2) for full oracle portability and reliability either use TO_CHAR with a
specific date format, ie, TO_CHAR('022205 15:22', 'RRMMDD HH24:MI')
3) or, if you could explicitly set the format for your session with ALTER
SESSION SET NLS_DATE_FORMAT = 'the format of your choice'
check the SQL manual at tahiti.oracle.com for addtional date formats
and _please_ ignore any examples that you ever see, including in Oracle docs
and courseware and OCP stuff, that assume a specific date format without
using one of these three techniques|||What is Oracle?|||On 22 Jan 2005 10:03:57 -0800, Max wrote:

>What is Oracle?
http://en.wikipedia.org/wiki/Oracle
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 22 Jan 2005 09:37:04 +0100, Thomas Jerkins wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
Hi Tom,
Most of the answers you got relate to Oracle. The error code indicates
that that's what you want. But since you posted in a SQL Server newsgroup,
the correct answer for SQL Server is:
a) Use one of the unambiguous formats:
- yyyymmdd for date only
- yyyy-mm-ddThh:mm:ss for date plus time
- yyyy-mm-ddThh:mm:ss.ttt for date plus time including milliseconds
b) Use explicit conversion using a style parameter (look up CONVERT and
CAST in Books Online for a list of all supported styles)
c) Use any format that matches your regional settings and pray that nobody
changes the regional setting and that you enver have to export your code
to other countries.
Actually, I recommend using only one of the first two options. Option a
takes the least number of keystrokes.

>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
Not for SQL Server, it isn't. Even though it won't fail for all settings,
it will fail for some.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Detecting Expanded Groups, etc...

This is a multi-part message in MIME format.
--=_NextPart_000_0006_01C75F65.85B0F190
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Is it possible to base an expression on whether a group is expanded or = not?
I want to show totals in the header, unless the group is expanded. In = that case, I want to show totals in the footer.
This would have to be dynamic, but i don't know if you have that kind of = access to the report objects when the report is being displayed. = Thanks.
J
--=_NextPart_000_0006_01C75F65.85B0F190
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Is it possible to base an expression on = whether a group is expanded or not?

I want to show totals in the header, = unless the group is expanded. In that case, I want to show totals in the footer.

This would have to be dynamic, but i = don't know if you have that kind of access to the report objects when the report is = being displayed. Thanks.

J
--=_NextPart_000_0006_01C75F65.85B0F190--On Mar 5, 9:33 pm, <rlrc...@.newsgroups.nospam> wrote:
> Is it possible to base an expression on whether a group is expanded or not?
> I want to show totals in the header, unless the group is expanded. In that case, I want to show totals in the footer.
> This would have to be dynamic, but i don't know if you have that kind of access to the report objects when the report is being displayed. Thanks.
> J
As far as I know, this functionality does not exist.
Regards,
Enrique Martinez
Sr. SQL Server Developersql

Detecting bad date with YYYYMMDD format

Every night we get a big dump of data from one of our outside financial
companies. In the file, one of the columns is a date, with the
following format:
YYYYMMDD
When we migrate this data into our database, SQL Server will
automaically cast this 8-character "varchar" field to a smalldatetime
field. This means that I can do this:
UPDATE table
SET datefield = tb.datefield
FROM loadedfile tb
However, I recently discovered that they sent us a few bad dates
(20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value."
So, my question is -- how do I go about detecting that the 8-character
string is not a valid date, while doing it on-the-fly inside the UPDATE
statement?Do your import into a seperate table with generic data types. Add a column
to the head of the table that indicates the attempted date of import. Add
another column that will indicate the type of error that you are having
with the column.
Now, do the import to this table and update column1 with getdate and column2
with NULL.
Next, run a routine that will validate all the data and update column2 from
null to something that indicates error.
Finally, do your normal import from this new table only where column2 is
null. Now you have a history of all the issues.
When everything is complete, if there is a count(*) > 0 in this table,
meaning that there were errors, you can email someone or just toss red
flags how ever you do this.
You can also check count(*) to see if > 0 and if true then you might not
want to do any of the import process and throw your flags.

> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
new|||You can use the ISDATE() function, which returns 1 if valid and 0 if not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jsevlie@.gmail.com> wrote in message news:1132076310.299318.326260@.g43g2000cwa.googlegroups
.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||You can use a WHERE clause to limit the rows processed, or use CASE within
the statement.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
<jsevlie@.gmail.com> wrote in message
news:1132076310.299318.326260@.g43g2000cwa.googlegroups.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Try this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT ISDATE(@.Date)
It returns "1" (which is obviously not right!)
Howver, do this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT CAST(@.date AS smalldatetime)
And it throws the error I'm getting.
Then, I did this:
declare @.dyear varchar(10)
declare @.dmonth varchar(10)
declare @.dday varchar(10)
declare @.newdate varchar(10)
IF ISDATE(@.Date) = 1 BEGIN
set @.dyear = substring(@.Date, 1, 4)
set @.dmonth = substring(@.Date, 5, 2)
set @.dday = substring(@.Date, 7, 2)
set @.newdate = @.dyear + '-' + @.dmonth + '-' + @.dday
select @.Date, @.newdate, isdate(@.newdate)
END
And it still thinks my string is a valid date. What gives?|||BOL shows a SmallDateTime as:
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute.
A regular DateTime field will handled dates between the years 1753 and 9999.
So the first option is to store the data as a DateTime instead of a
SmallDateTime, and CAST the field to DateTime:
UPDATE table
SET datefield = CAST(tb.datefield AS DATETIME)
FROM loadedfile tb
If the field in table is SmallDateTime and you cannot change it, then you
will have to throw in some sort of business logic - what happens if the date
is > 2079? If it is to set the date to be entered as 1/1/2079, then you
could do something like this:
UPDATE table
SET datefield = CASE WHEN CAST(db.datefield AS DATETIME) > '1/1/2079'
THEN '1/1/2079'
ELSE CAST(tb.datefield AS SMALLDATETIME)
END
FROM loadedfile tb
This will check to see if the data passed in is too big, and if it is pass
in the date of '1/1/2079'. You could then query the table for anything with
the '1/1/2079' date and know it was something that was wrong - and follow up
to fix it.
Hope this helps some
Rob|||-- check for the smalldatetime limits
update table
set datefield = tb.datefield
from loadedfile tb
where tb.datefield>='19000101' and tb.datefield<='20790606'
-- report on those that are out of smalldatetime range
-- the implicit int conversion should be OK, unless other bad data is
there as well
select <col list>
from loadedfile
where datefield<'19000101' or datefield>'20790606'
you may want to change the datatype of the loadedfile table column to
datetime [not smalldatetime, to avoid errors] instead of varchar, then
you'd be comparing dates to dates.
jsevlie@.gmail.com wrote:
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Very nice, I like the idea about casting to a full datetime string and
then seeing if it passes a threshold. That should work, I'll give it a
try.
Thanks!

Detect new files and populate date

Hi, I need to create a SSIS package for following usage:

I have a folder called c:\test. Every month, I copy a file with following format testMMYY.txt. (Month Year) from a client (A) for vendor B. I have a sql table which contains filemonth(, clientname and vendorname. How can I create a SSIS package to detect any new files, if any, and populate the month and year into file month, and populate clientname and vendorname.

Thanks,

You can use the FileWatcher task (available on SQLIS.com) to monitor for a file. If it is only a once a month thing, you could just run the package and use the File System task to check for existence of the file. You'll need to use a expression to set the ConnectionString property of your FlatFile connection manager to the correct value by concatenating the proper month and year into the file name.|||

Thanks for the reply.

Maybe my description is a little bit misleading, my issue is to identify latest file and transform the file name, e.g. I0407.txt into April 2007 and insert it into the filemonth field in the vendorfile table.

Any thoughts?

Thanks,

|||You can use a script task and the DateAndTime class's MonthName function to translate the file name. This post (http://forums.microsoft.com/msdn/showpost.aspx?postid=1467831&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1) has the script for sorting the list of files in a directory and outputting in a DataTable object. Just take the last row of the data table for the latest file.

design advice...writing a text file

I need to create a text file using information from SQL tables/views in the following format...Can anyone recommend a direction or procedure to look into, i.e, sql script, custom dts, etc. The items in parentheses identify specific portions of the text file.

(01)
101081,84423,customer ,072304,customer ,11310 Via Playa De Cortes , ,San Diego ,CA,92124,
(02) 6 ,1 , , , , ,22 ,1 ,0.00 ,160.46 ,160.46 ,0.00 , , , , , , , , ,1,1
(03)B130907540,5.41 ,1
(03)B130907550,5.41 ,1
(03)B130907560,5.41 ,1
(03)B130907570,6.04 ,1
(03)B065007550,1.72 ,2
(03)B065007560,1.72 ,6
(03)B519926530,4.66 ,13
(03)B519926550,4.66 ,12
(03)B560911200,2.14 ,1
(03)B560912500,2.14 ,1
(03)B095305750,3.65 ,1This looks a lot like EDI format to me. Maybe it is just because of all the bad memories of it. Given a choice, I would go with a scripting language outside of SQL Server. Either PERL or VB Script. I believe PERL was designed with such file formats in mind, and it is not that hard to learn.|||Perl would make the solution easier to code. VBA could be incorporated into a DTS package, which would be a bunch more portable (and easier to write if you already know VB and don't know Perl).

Pick your poison. Either Perl or VBA would work nicely, and each has its own benefits.

-PatP|||Actually EDI (X12) looks more like this:

CAS*PR*1*24**2*12~CAS*CO*45*40~...etc., all one line.|||Could well be I have the wrong name for it, then. Like I say, it has been a while. The format I had to deal with was:

header row
first item header
first item detail
first item detail
first item footer
second item header
...
...
footer row.

A very nested and finicky format. In my first job, I spent a number of weeks trying to get an output that would work, but kept getting blank lines in my output. Nowadays, I look back on that and laugh. Probably take an hour with different tools. Back then, I was truly "a man with a hammer".|||The fastest way to get data out of SQL to text is using a BCP (bulk copy paste). I've written a few EDI formats for medicare/medicaid billing and such using BCP and it's works like a charm. Get all of the data together in a temp table first and then use something like this in a stored procedure to export the data:

SET @.EXPORTSQL=
'BCP "SELECT * FROM ##TEMPTABLE" QUERYOUT C:\FILE.TXT -c -t,'

EXEC MASTER..XP_CMDSHELL @.EXPORTSQL

This will export a comma separated values version of the temp table to a file. I have to upload mine to an FTP site, so I have a mapped drive on the server attached to that FTP site and then change the path to that mapped drive letter. The proc is then effectively creating the file and uploading in less than a second or two.

In my experience DTS is great, but there's not need to over complicate the product when a couple lines of SQL can get you there!