db backup simple vs. full recovery mode

When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?

When you do a backup, markers are written to the Transaction Log file, however, the backup process does not change anything about the datafiles -therefore the 'trn' file gets a new datetime and the data file does not.

The Transaction Log file does not shrink UNLESS specifically so instructed. See Books Online for DBCC 'Shrinkfile'.

|||

Hi,

You can schedule half/hourly t-log backup to keep it in shape, how ever if its growing unpexctingly refer below thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221599&SiteID=1

Hemantgiri S. Goswami

|||

What we are seeing are current timestamps on the trn file, current timestamps on the ldf, but about a six month old modified date on the mdf. I would assume that the trn file would have the most recent transactions, the ldf the intermediate, and then the mdf.

With the truncate command on the trn file, do the transactions immediately hit the mdf file or the ldf (I would think the ldf)? however when does the mdf get updated by the ldf file?

Am I completely lost--I thought that the ldf (a locked mdf file, correct?) would eventually post the edits/updates to the mdf.

|||

The ldf is the transaction log file. Data changes are moved to the mdf (data file) on a regular basis -usually within seconds.

The OS stamps the file date. SQL Server has a data file (mdf) open with a, perhaps, large, amount of empty space. The OS does not know what is happening inside the mdf file unless there are specific interactions between SQL Server and the OS regarding the file.

It seems like you are confused because the mdf file date is not changing. It most likely will not change unless one of the following actions occur: Filegrowth, Fileshrink, Detach/Attach.

DB Backup Question

Hello,
I know the transaction log will grow while backing up a
database, but will the database file grow as well if the
database is not in use?
Any help would be greatly appreciated!
Thanks in advance.
If the Db is not being actively updated i would not expect either the
databse, or the log to grow t all during a backup.
I am not sure what you mean when you say you "know the log will grow". Is
there an underlying problem you are trying to resolve?
Mike John
"Gene S." <anonymous@.discussions.microsoft.com> wrote in message
news:21b801c4ac8b$c816de30$a601280a@.phx.gbl...
> Hello,
> I know the transaction log will grow while backing up a
> database, but will the database file grow as well if the
> database is not in use?
> Any help would be greatly appreciated!
> Thanks in advance.
sql

DB Backup Question

Hello,
I know the transaction log will grow while backing up a
database, but will the database file grow as well if the
database is not in use?
Any help would be greatly appreciated!
Thanks in advance.If the Db is not being actively updated i would not expect either the
databse, or the log to grow t all during a backup.
I am not sure what you mean when you say you "know the log will grow". Is
there an underlying problem you are trying to resolve?
Mike John
"Gene S." <anonymous@.discussions.microsoft.com> wrote in message
news:21b801c4ac8b$c816de30$a601280a@.phx.gbl...
> Hello,
> I know the transaction log will grow while backing up a
> database, but will the database file grow as well if the
> database is not in use?
> Any help would be greatly appreciated!
> Thanks in advance.

DB Backup Maint Plan - old file deletes

Hello. When using a DB Maint Plan to backup databases,
say 10 databases all in one plan... the job backs up each
database and THEN at the end of the entire job, it deletes
the previous backups (based on your backup retention).
question>> is there any way to backup the database, and
RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
backup files? The problem is that you need enough disk
space to hold the current and old backups for ALL
databases being backed up, unless there's some setting
we're missing? Any thoughts on that? (using DB Maint
Plans, I know we could code things ourselves and NOT use
Maint Plans)... THanks, BruceThis is a multi-part message in MIME format.
--=_NextPart_000_03A4_01C3B337.52A2A5B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
If you're cramped for space, consider replacing the single maintenance plan
with one plan for each DB. This way, the old backup for a database is
removed before the next plan kicks in.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
Hello. When using a DB Maint Plan to backup databases,
say 10 databases all in one plan... the job backs up each
database and THEN at the end of the entire job, it deletes
the previous backups (based on your backup retention).
question>> is there any way to backup the database, and
RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
backup files? The problem is that you need enough disk
space to hold the current and old backups for ALL
databases being backed up, unless there's some setting
we're missing? Any thoughts on that? (using DB Maint
Plans, I know we could code things ourselves and NOT use
Maint Plans)... THanks, Bruce
--=_NextPart_000_03A4_01C3B337.52A2A5B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If you're cramped for space, consider =replacing the single maintenance plan with one plan for each DB. This way, =the old backup for a database is removed before the next plan kicks =in.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce de Freitas" wrote in =message news:0fca01c3b360$91=c86e90$a401280a@.phx.gbl...Hello. When using a DB Maint Plan to backup databases, say 10 databases all =in one plan... the job backs up each database and THEN at the end of the =entire job, it deletes the previous backups (based on your backup =retention). question>> is there any way to backup the database, =and RIGHT THEN, in a Maint Plan job, delete the PREVIOUS backup =files? The problem is that you need enough disk space to hold the current =and old backups for ALL databases being backed up, unless there's some =setting we're missing? Any thoughts on that? (using DB Maint =Plans, I know we could code things ourselves and NOT use Maint Plans)... =THanks, Bruce

---

--=_NextPart_000_03A4_01C3B337.52A2A5B0--|||Yep, we talked about that as a possibility. It's nice to
have DB backups in one Maint Plan though sometimes, and
was wondering if there was a way to do that. THanks
Tom... Bruce
>--Original Message--
>If you're cramped for space, consider replacing the
single maintenance plan
>with one plan for each DB. This way, the old backup for
a database is
>removed before the next plan kicks in.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
>Hello. When using a DB Maint Plan to backup databases,
>say 10 databases all in one plan... the job backs up each
>database and THEN at the end of the entire job, it deletes
>the previous backups (based on your backup retention).
>question>> is there any way to backup the database, and
>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
>backup files? The problem is that you need enough disk
>space to hold the current and old backups for ALL
>databases being backed up, unless there's some setting
>we're missing? Any thoughts on that? (using DB Maint
>Plans, I know we could code things ourselves and NOT use
>Maint Plans)... THanks, Bruce
>|||This is a multi-part message in MIME format.
--=_NextPart_000_048B_01C3B33F.F3F4BCC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Maintenance plans give you ease of use, but that does not always translate
to "most efficient".
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:109a01c3b368$e3450d70$a401280a@.phx.gbl...
Yep, we talked about that as a possibility. It's nice to
have DB backups in one Maint Plan though sometimes, and
was wondering if there was a way to do that. THanks
Tom... Bruce
>--Original Message--
>If you're cramped for space, consider replacing the
single maintenance plan
>with one plan for each DB. This way, the old backup for
a database is
>removed before the next plan kicks in.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
>Hello. When using a DB Maint Plan to backup databases,
>say 10 databases all in one plan... the job backs up each
>database and THEN at the end of the entire job, it deletes
>the previous backups (based on your backup retention).
>question>> is there any way to backup the database, and
>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
>backup files? The problem is that you need enough disk
>space to hold the current and old backups for ALL
>databases being backed up, unless there's some setting
>we're missing? Any thoughts on that? (using DB Maint
>Plans, I know we could code things ourselves and NOT use
>Maint Plans)... THanks, Bruce
>
--=_NextPart_000_048B_01C3B33F.F3F4BCC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Maintenance plans give you ease of =use, but that does not always translate to "most efficient".
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce de Freitas" wrote in =message news:109a01c3b368$e3=450d70$a401280a@.phx.gbl...Yep, we talked about that as a possibility. It's nice to have DB =backups in one Maint Plan though sometimes, and was wondering if there was a =way to do that. THanks Tom... Bruce>--Original Message-->If you're cramped for space, consider replacing the single maintenance plan>with one plan for each DB. This =way, the old backup for a database is>removed before the next plan =kicks in.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Bruc=e de Freitas" wrote in message>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...>Hell=o. When using a DB Maint Plan to backup databases,>say 10 databases =all in one plan... the job backs up each>database and THEN at the end of =the entire job, it deletes>the previous backups (based on your backup =retention).>>question>> is there any way to =backup the database, and>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS>backup files? The problem is that you need enough disk>space to hold the current and old backups for =ALL>databases being backed up, unless there's some setting>we're missing? =Any thoughts on that? (using DB Maint>Plans, I know we could =code things ourselves and NOT use>Maint Plans)... THanks, Bruce>

---

--=_NextPart_000_048B_01C3B33F.F3F4BCC0--

DB Backup Job

Hi All,

I have a db backup job on SQL Server 2005 server that has a delete step that deletes a backup file that is located on the SQL Server 2000 server. Here is the step:

EXEC master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'

The step doesn't create any errors but it doesn't delete the file. When I run the same command from the command prompt it deletes the file.

I can't figure out what is wrong. Any Idea?

Thanks.Try using Query Analyzer to execute:DECLARE @.foo INT
EXEC @.foo = master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'
SELECT @.foo AS CmdErrorLevelBoth the output and the error level ought to give you clues. My first guess is that the Windows Login being used by xp_cmdshell doesn't have permissions.

-PatP|||I get an Access Denied error and CmdErrorLevel 1. How can I find out which account is used by xp_cmdshell? And what permissions does this accout need?|||it needs sysadmin rights. I think it is using whatever account is running the SQL agent. This account also needs write access to the destination.|||How can I find out which account is used by xp_cmdshell?
sp_xp_cmdshell_proxy_account (http://msdn2.microsoft.com/en-us/library/ms190359.aspx) is where you can set it.what permissions does this accout need?This is the $64,000 question. Microsoft recommends little or no permissions, and I generally agree with them because of the security risks.

I would recommend making the step in the backup job a command step instead of a SQL step. That way you can set the Windows Credentials there, and you don't need to open up xp_cmdshell at all.

-PatP|||How do you set the credentials?|||Check the sql agent service in services.msc. If it's running with a domain account, then that account needs to have access to delete from \\DevServerName\Dev-bkups-db\BackupsDB\DBName\. It doesn't necessarily need admin rights to DevServerName.|||It is running with a local system account.|||local system account can not access network resources.|||Which account should be SQL Agent service startup account?|||typically I have the network guys create a LAN account dedicated to this purpose with a password that does not expire. This last part is very important.|||Thanks for your help.|||How do you set the credentials?The easy way is to change the job owner. There are sneaky ways too, especially if you've installed the Windows Resource Kit or are willing to write a bit of VBA code.

-PatP|||Thrasymachus,

Forgot to ask what are the minimum rights does this account need?

dayly table update

hello,
i must dayly update a table in my database with the values of a CSV file
(~300000 entries)
example of the tabel (artNr ,productname ,price )
000001 monitor 234,66
000003 pc 699,44
....
245433 router 126,33
Now dayly the table-content is deleted and the csv-file is imported
Is it possible a better way - to update only the modified values and insert
the new.
How can this be done?
thanksOne recommendation could be
1. Create a staging table called get_bcp_h_daily_csv
2. Truncate the table
3. DTS the csv file into staging table
4. Write the first entry to a surrogate table called ot_su_daily_csv as in
a) below.
5. Write a sProc that incrementally loads what's in the surrogate table into
a lookup table called ot_lu_daily_csv for your database as in b) below:
6. Schedule a job to run this DTS Each day
7. Sorted
a)
INSERT INTO ot_su_daily_csv (ColName1, ColName2)
SELECT ColName1, ColName2
FROM get_bcp_h_daily_csv BCP
WHERE NOT EXISTS ( SELECT * FROM ot_su_daily_csv SURR
WHERE SURR.Col1= BCP.Col1 )
b.)
INSERT INTO ot_lu_daily_csv
(Col1, Col2)
SELECT Col1, Col2
FROM ot_su_daily_csv SURR(nolock)
ORDER BY Col1|||thanks for the recommendation - it works well if only each day new values in
the csv-file are attached.
But in my csv file some colums of the articles are changed - like in the
example
example: - day1
000001 monitor 234,66
000003 pc 699,44
the next day - day 2
000001 monitor 230,03 (price is modified...)
000003 pc-3,4GHz 699,44 (product description is modified)
245433 router 126,33 -> ok will be detected and updated
....
how to make a correct update in this situation ...
thanks
Xavier|||On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:

>thanks for the recommendation - it works well if only each day new values i
n
>the csv-file are attached.
>But in my csv file some colums of the articles are changed - like in the
>example
>example: - day1
>000001 monitor 234,66
>000003 pc 699,44
>the next day - day 2
>000001 monitor 230,03 (price is modified...)
>000003 pc-3,4GHz 699,44 (product description is modified)
>245433 router 126,33 -> ok will be detected and updated
>....
>how to make a correct update in this situation ...
>thanks
>Xavier
Hi Xavier,
Load the new data in a staging table. Then run a procedure that updates
existing data and adds new data, as follows:
UPDATE t
SET Descr = s.Descr,
Price = s.Price,
.. (other columns)
FROM TheTable AS t
INNER JOIN StagingTable AS s
ON s.KeyColumn = theTable.keyColumn
WHERE t.Descr <> s.Descr
OR t.Price <> s.Price
OR ... (other columns)
INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
SELECT KeyColumn, Descr, Price, ... (other columns)
FROM Stagins AS s
WHERE NOT EXISTS
(SELECT *
FROM TheTable AS t
WHERE t.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks,
Xavier
"Hugo Kornelis" wrote:

> On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:
>
> Hi Xavier,
> Load the new data in a staging table. Then run a procedure that updates
> existing data and adds new data, as follows:
> UPDATE t
> SET Descr = s.Descr,
> Price = s.Price,
> ... (other columns)
> FROM TheTable AS t
> INNER JOIN StagingTable AS s
> ON s.KeyColumn = theTable.keyColumn
> WHERE t.Descr <> s.Descr
> OR t.Price <> s.Price
> OR ... (other columns)
> INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
> SELECT KeyColumn, Descr, Price, ... (other columns)
> FROM Stagins AS s
> WHERE NOT EXISTS
> (SELECT *
> FROM TheTable AS t
> WHERE t.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

Datetime to time conversion with default date

Hi,

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?

Thanks,

Try this in a Derived Column transform (replace DateValue with the name of your column):

Code Snippet

(DT_DBTIMESTAMP)("1900-01-01 " + (DT_WSTR,10)(DT_DBTIME)DateValue)

|||

Thanks, jwelch.

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data
.
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:4
5:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_Genera
l_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL
,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample da
ta..
>
> This table has about 2 million rows.There is a date field in the table whi
ch
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS fl
ag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14
:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_Gene
ral_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NU
LL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helps
Assume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample data..
>
> This table has about 2 million rows.There is a date field in the table which
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:
> Hi guys
> I exported some data from a text file to sql server. Here is the sample data..
>
> This table has about 2 million rows.There is a date field in the table which
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?
It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>
|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>> Using SQL2005 DTS - I am trying to import data from a CSV file into a
>> table created with the following
>> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
>> [Date-Time] datetime,
>> [Server] text,
>> [Drive] text,
>> [Drive Size] numeric(29,0),
>> [Space Free] numeric(29,0)
>> )
>> the first field is date and time and looks like this >>
>> 09/20/2006 06:30:03 PM
>> But no matter what I try to use for a final field format the result of
>> that data after it's imported displays the same time for every record >>
>> 12:00:00 AM <<. The date comes through fine, but it just does not seem
>> to recognize the time. What do I need to do to get the time to be
>> imported correctly ?
>

Datetime conversion from csv file

I have a DTS-package running which imports data from a .csv file to a sql2000 database.
In the file there are some datefields in dd/mm/yyyy format and i want to keep it that way. But after the import the dateformat is yyyy/mm/dd.
Does anybody know how i can prevent this from happening?

Thanks in advanceIn MS-SQL a datetime field is typically displayed as 'yyyy/mm..'. Internally it is stored as an 8-byte value counting from 1973. If you'd like to change the way the datetime is shown, I'd suggest to use 'convert'.

dateformat is ignored

Hello,

I receive a file containing some character fields along with a date.
The date values in the file are formatted as "dd/mm/yy", that is
2-digit day, 2-digit month, and 2-digit year. The separator could be
slash or a dash ("-"). The file is in a proprietary format, and bcp is
not an option.

So, I decided to load the file using a prepared statement. I open a
cursor with an INSERT statement, read from the file, parse out values,
and put it in the database using the cursor. All is OK; except that
the date values are mangled. This is despite the fact that I am issuing
a "set dateformat dmy" before running the INSERT statement.

It seems that the "set dateformat dmy" is not being accepted, or it is
being ignored. I set it at the beginning right after opening a
connection to the database. From what I understand, it should work.
Am I doing something wrong? Any suggestions on how to get this to
work?

Thanks!newtophp2000@.yahoo.com wrote:

> Hello,
> I receive a file containing some character fields along with a date.
> The date values in the file are formatted as "dd/mm/yy", that is
> 2-digit day, 2-digit month, and 2-digit year. The separator could be
> slash or a dash ("-"). The file is in a proprietary format, and bcp is
> not an option.
> So, I decided to load the file using a prepared statement. I open a
> cursor with an INSERT statement, read from the file, parse out values,
> and put it in the database using the cursor. All is OK; except that
> the date values are mangled. This is despite the fact that I am issuing
> a "set dateformat dmy" before running the INSERT statement.
> It seems that the "set dateformat dmy" is not being accepted, or it is
> being ignored. I set it at the beginning right after opening a
> connection to the database. From what I understand, it should work.
> Am I doing something wrong? Any suggestions on how to get this to
> work?
> Thanks!

You say BCP isn't an option but you didn't explain what other method
you are using to read the file or why a cursor is necessary. Don't rely
on SET DATEFORMAT. Use the CONVERT function with the style parameter to
specify the exact format. Looks like style 3 or 103 is what you need.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> You say BCP isn't an option but you didn't explain what other method
> you are using to read the file or why a cursor is necessary. Don't rely
> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
> specify the exact format. Looks like style 3 or 103 is what you need.

I read from the file line by line and parse the line to extract the
fields. I then use the bound variables in the prepared Insert
statement to add it to the database. I wanted to change the DATEFORMAT
configuration as it seemed to be such a straight answer. I guess I
could use the CONVERT function if it is fast enough. I can do some
tests to see how it performs.

I am curius: is there a particular reason to shy away from setting
DATEFORMAT? Is it not reliable as implemented or something else?

Thanks a lot!

> --
> David Portas
> SQL Server MVP
> --|||Hi

If you are parsing a string then you constructing the date in CCYYMMDD
format will be a safe option.

John

<newtophp2000@.yahoo.com> wrote in message
news:1135777730.480129.321010@.z14g2000cwz.googlegr oups.com...
> David Portas wrote:
>> You say BCP isn't an option but you didn't explain what other method
>> you are using to read the file or why a cursor is necessary. Don't rely
>> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
>> specify the exact format. Looks like style 3 or 103 is what you need.
>
> I read from the file line by line and parse the line to extract the
> fields. I then use the bound variables in the prepared Insert
> statement to add it to the database. I wanted to change the DATEFORMAT
> configuration as it seemed to be such a straight answer. I guess I
> could use the CONVERT function if it is fast enough. I can do some
> tests to see how it performs.
> I am curius: is there a particular reason to shy away from setting
> DATEFORMAT? Is it not reliable as implemented or something else?
> Thanks a lot!
>
>> --
>> David Portas
>> SQL Server MVP
>> --|||David and John,

Thank you very much for your input. I am now using the techniques that
you suggested and it works great!

Date/Time stamp

Hi All,

I have a script that adds the date/time stamp to a file in the following format:

200701120149PM.

here is the script:

set dttm=%~t1
for /F "tokens=1-6 delims=/: " %%i in ("%dttm%") do (

set date=%%k%%i%%j%%l%%m
)

I need to display the time as military. How can I do that?

Thanks.What is that?

Can't you use T-SQL?

What's the table definition (DDL) look like?|||This is a DOS command that displays the system date/time.
What table are you refering to?|||Well, since this is a MS SQL Server forum, you might want to ask a question about that, othwerwise, there might be another board that can help you out with DOS|||Definitely the wrong forum for this question. Nevertheless, I think the answer may depend on your regional settings; when I run it, the output is fine (ie, no AM/PM, just a military hour).

This site (http://www.robvanderwoude.com/index.html)has some good stuff on DOS scripts...

Regards,

hmscott