How do I loop through dataset then replace?

How would I loop through the rows of a dataset, then replace certain character in a certain column?

I have a database which has a date field formatted 23/08/2007, I wish to loop through the dataset containing the results from the dataset, change the format of the date to 23.08.2007 then store the value back into the dataSet, which is called 'dataSet', and the table is called 'News'.

Using C# by the way.

Thanks in advance

I suggest you to do this in database level though stored procedure that is dynamic (receive your changing criteria via input parameters).
Looping in stored procedures can be done though cursor (I know it is not recomended to use cursor in database for performance issue, but I think in this case it will be much better than looping into your application).

Tip:
You can get many date format for your DATETIME column in database. For example try: Convert(varchar(25), MyDateTimeColumn,113) or Convert(varchar(25), MyDateTimeColumn,111) [Used SQL Books Online for more samples and examples].

Good luck.

|||

Thanks

I'm new to this, so I'll read up on stored procedures, infact, I'm sure I have a whole book on SQL somewhere.

|||

While I read up on stored procedures can someone tell me how to do this in the application? The dataset will only contain 3 rows so it shouldnt have too much of an effect on the application.

|||

If it is just 3 records, then it will not affect the performance that much!

Here is the idea:
Let yourSqlDataAdapterfillyourDataSet.
Now, you have the data in the DataSet, loop into those records in DataSet.
Use String.Replace for replacing issue you have.

http://msdn2.microsoft.com/en-us/library/system.string.replace(VS.71).aspx

Good luck.

|||

What I wanted to do was get data from a database in date order, then alter the format of the date once it was in the dataset.

Instead of faffing about with it, I simply set the date in the database twice, i.e. Date, then a display date.

Now the data is sorted using the Date, but it's the displayDate field which displays the date to the user.

Thanks for all the help

How do I include all dates between 2 parameters in a table?

I'm working with a data set where some values have an aggretate total
(for example, a count of something) in a certain date range and others
do not. The problem I am having is that I need each page of the report
to show all the possible dates and the display a 0 when there are no
values for the date. Currently it is dropping that date.
For Example:
@.StartDate = 1/1/06
@.EndDate = 6/31/06
-- I get this on grouped values where there are empty months:
Jan06 5
Feb06 3
Apr06 7
Jun06 10
-- But I want this:
Jan06 5
Feb06 3
Mar06 0
Apr06 7
May06 0
Jun06 10
Due to other report properties and calculations, I can't really modify
my query to fix this problem. It seems like it should be easy to solve
but I'm not getting it!!!
Thanks.I think you must be using table control. so no way you can substitute 0 for
missing one. you need to modify query to use right join to include all the
rows.
if you hard code all the months value still you need to change the query to
get all the rows to column..
Try to change query, rather than breaking head.
Amarnath
"CanoAko" wrote:
> I'm working with a data set where some values have an aggretate total
> (for example, a count of something) in a certain date range and others
> do not. The problem I am having is that I need each page of the report
> to show all the possible dates and the display a 0 when there are no
> values for the date. Currently it is dropping that date.
> For Example:
> @.StartDate = 1/1/06
> @.EndDate = 6/31/06
> -- I get this on grouped values where there are empty months:
> Jan06 5
> Feb06 3
> Apr06 7
> Jun06 10
> -- But I want this:
> Jan06 5
> Feb06 3
> Mar06 0
> Apr06 7
> May06 0
> Jun06 10
> Due to other report properties and calculations, I can't really modify
> my query to fix this problem. It seems like it should be easy to solve
> but I'm not getting it!!!
> Thanks.
>|||Hmm... that doesn't really help. I admit now that I will probably have
to design a query to do it. The problem is that the query joins 2
tables and the date that I'm pulling is on the second table. So the
date doesn't exist to do an aggregate with a total of 0 in the first
place.
I'm going to try it with CASE to force it to happen, but I'm not too
excited about that. I've only been doing this for a month so I still
haven't figured everything out.
Amarnath wrote:
> I think you must be using table control. so no way you can substitute 0 for
> missing one. you need to modify query to use right join to include all the
> rows.
> if you hard code all the months value still you need to change the query to
> get all the rows to column..
> Try to change query, rather than breaking head.
> Amarnath
>
> "CanoAko" wrote:
> > I'm working with a data set where some values have an aggretate total
> > (for example, a count of something) in a certain date range and others
> > do not. The problem I am having is that I need each page of the report
> > to show all the possible dates and the display a 0 when there are no
> > values for the date. Currently it is dropping that date.
> >
> > For Example:
> >
> > @.StartDate = 1/1/06
> > @.EndDate = 6/31/06
> >
> > -- I get this on grouped values where there are empty months:
> > Jan06 5
> > Feb06 3
> > Apr06 7
> > Jun06 10
> >
> > -- But I want this:
> > Jan06 5
> > Feb06 3
> > Mar06 0
> > Apr06 7
> > May06 0
> > Jun06 10
> >
> > Due to other report properties and calculations, I can't really modify
> > my query to fix this problem. It seems like it should be easy to solve
> > but I'm not getting it!!!
> >
> > Thanks.
> >
> >|||Hi,
thats quite easy, use the following link to see how to generate a
calendar table, you can either persits it or create it on the fly.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

how do i get date into dd-mm-yy format?

i have the following in an insert stored proc

CONVERT(CHAR(8),GETDATE(),10)

but this inserts 03-06-06.

how do i get it to 06-03-06?

Cheers,
Craig

Probably by setting the machine you're running on to run in a time zone where this format is standard ?|||

my sp is as follows:-

IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC insert for YES consent where a ConsentDate is supplied
BEGIN
INSERT INTO tblStudyServices
(SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
ReadmissSixMonths, Consent, ConsentDate, CreationDate)
VALUES
(@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs, @.intFourRegularDrugsNo, @.blnHRD,
@.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate, CONVERT(CHAR(8),GETDATE(),10))
END

@.dteActualConsentDate is passed in as 14-03-06

if i then look in the table it has

CosentDate = 14-03-06
CreationDate = 03-06-06

i have tried SET_DATEFORMAT dmy

but still no luck

|||Datetime values are not stored in any special format. There are special allowed format and rules for inserting datetime. For output, it is the client application that formats the value to something that is human-readable. See http://www.karaszi.com/SQLServer/info_datetime.asp for more information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:3207581d-2ba8-4191-b788-7db50a967db0@.discussions.microsoft.com... >i have the following in an insert stored proc >
> CONVERT(CHAR(8),GETDATE(),10) >
> but this inserts 03-06-06. >
> how do i get it to 06-03-06? >
> Cheers,
> Craig >
>|||Did you see the reply I posted? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:acc61a6e-96e2-4412-9e4f-5ffdd7ab7b62@.discussions.microsoft.com...
> my sp is as follows:- >
> IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC
> insert for YES consent where a ConsentDate is supplied
> BEGIN
> INSERT INTO tblStudyServices
> (SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
> ReadmissSixMonths, Consent, ConsentDate, CreationDate)
> VALUES
> (@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs,
> @.intFourRegularDrugsNo, @.blnHRD,
> @.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate,
> CONVERT(CHAR(8),GETDATE(),10))
> END >
> @.dteActualConsentDate is passed in as 14-03-06 >
> if i then look in the table it has >
> CosentDate = 14-03-06
> CreationDate = 03-06-06 >
> i have tried SET_DATEFORMAT dmy >
> but still no luck >
>

How do I Filter by Current Date

I have used 'GETDATE()' but it appears to ignore the current date
because the data does nor have any time information in it. How do I get
filter the data just by date?
Regards
Colin
*** Sent via Developersdex http://www.examnotes.net ***Select *
From TableName
Where DateColumn = Cast(DateDiff(d, 0, Current_Timestamp) As DateTime)
Thomas
"Colin Spalding" <pupil@.alottolearn.com> wrote in message
news:eRPb0%23FZFHA.3164@.TK2MSFTNGP09.phx.gbl...
>I have used 'GETDATE()' but it appears to ignore the current date
> because the data does nor have any time information in it. How do I get
> filter the data just by date?
> Regards
> Colin
> *** Sent via Developersdex http://www.examnotes.net ***|||Colin Spalding wrote:
> I have used 'GETDATE()' but it appears to ignore the current date
> because the data does nor have any time information in it. How do I
> get filter the data just by date?
> Regards
> Colin
> *** Sent via Developersdex http://www.examnotes.net ***
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Colin Spalding wrote:
> I have used 'GETDATE()' but it appears to ignore the current date
> because the data does nor have any time information in it. How do I
> get filter the data just by date?
>
If you're storing time as well as date, then use greater than and less than:
WHERE datefield >= GETDATE() and datefield < dateadd(d,1,GETDATE())
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Bob,
My guess is that you typed in getdate(), but meant @.d, where
@.d is a date-only value that has the date required, and what you
ended up suggesting was an uncommon requirement: rows with [datefield]
some time in the 24 hour interval starting right this instant? If the
datefield values are all timestamped midnight, it will give the rows
from tomorrow, and otherwise, it may not be reproducible.
If "today" is needed, one solution is this:
where datefield >= dateadd(d,datediff(day,0,getdate()),0)
and datefield < dateadd(d,datediff(day,0,getdate()),1)
Steve Kass
Drew University
Bob Barrows [MVP] wrote:

>Colin Spalding wrote:
>
>If you're storing time as well as date, then use greater than and less than
:
>WHERE datefield >= GETDATE() and datefield < dateadd(d,1,GETDATE())
>Bob Barrows
>
>|||Steve Kass wrote:
> Bob,
>
> My guess is that you typed in getdate(), but meant @.d, where
> @.d is a date-only value that has the date required,
Yes. My bad
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Or alternatively (rather than the date calculations),
WHERE CONVERT(char(8),datefield,112) = CONVERT(char(8),GETDATE(),112)
as the 112 conversion style, will drop the time info and format the date
in an unambiguous format.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bob Barrows [MVP] wrote:

>Steve Kass wrote:
>
>Yes. My bad
>

How do I figure out time and date at the same time?

Dear all,
I've got some problems with date and time (very silly, I know)
select * from crm_1 where log < datepart(yyyy,getdate())
and log < datepart(mm,getdate())
and log < datepart(dd,getdate())
The aforementioned query doesn't find this value:
2005-06-09 08:27:17.810
CREATE TABLE [dbo].[CRM_1] (
[ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
[log] [datetime] NOT NULL)
GO
Thanks a lot and regards,I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...ime
.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
> Dear all,
> I've got some problems with date and time (very silly, I know)
> select * from crm_1 where log < datepart(yyyy,getdate())
> and log < datepart(mm,getdate())
> and log < datepart(dd,getdate())
> The aforementioned query doesn't find this value:
> 2005-06-09 08:27:17.810
> CREATE TABLE [dbo].[CRM_1] (
> [ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> [log] [datetime] NOT NULL)
> GO
> Thanks a lot and regards,|||Try this:
select * from crm_1 where datepart(yyyy,log) < datepart(yyyy,getdate())
and datepart(mm,log) < datepart(mm,getdate())
and datepart(dd,log) < datepart(dd,getdate())
Or better yet, do some more reading as Tibor suggests.
ML|||It has been very useful. thanks a milion
"Tibor Karaszi" wrote:

> I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...i
me.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
>

How do I display SQL Server 1/1/1900 dates as blank in Access?

Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
that SQL Server 2005 considers a blank date as blank rather than as
"1/1/1900"? I know I can create a function like that shown below to do it.
Public Function BlankDate(datInputDate)
If datInputDate < #1/2/1900# Then
BlankDate = ""
Else
BlankDate = datInputDate
End If
End Function
I'm just hoping there's a simpler more universal way to do it, either on the
Access side or the SQL Server 2005 side.
Thanks!
Michael
I thought that blank date was null.
You could try in your query using:
nullif(FieldName, '1/1/1900') as FieldName
Note that this will not be updatable.
Russel Loski, MCSD.Net
"Michael Nagan" wrote:

> Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
> that SQL Server 2005 considers a blank date as blank rather than as
> "1/1/1900"? I know I can create a function like that shown below to do it.
> Public Function BlankDate(datInputDate)
> If datInputDate < #1/2/1900# Then
> BlankDate = ""
> Else
> BlankDate = datInputDate
> End If
> End Function
> I'm just hoping there's a simpler more universal way to do it, either on the
> Access side or the SQL Server 2005 side.
> Thanks!
> Michael
>
>

How do I display SQL Server 1/1/1900 dates as blank in Access?

Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
that SQL Server 2005 considers a blank date as blank rather than as
"1/1/1900"? I know I can create a function like that shown below to do it.
Public Function BlankDate(datInputDate)
If datInputDate < #1/2/1900# Then
BlankDate = ""
Else
BlankDate = datInputDate
End If
End Function
I'm just hoping there's a simpler more universal way to do it, either on the
Access side or the SQL Server 2005 side.
Thanks!
MichaelI thought that blank date was null.
You could try in your query using:
nullif(FieldName, '1/1/1900') as FieldName
Note that this will not be updatable.
--
Russel Loski, MCSD.Net
"Michael Nagan" wrote:
> Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
> that SQL Server 2005 considers a blank date as blank rather than as
> "1/1/1900"? I know I can create a function like that shown below to do it.
> Public Function BlankDate(datInputDate)
> If datInputDate < #1/2/1900# Then
> BlankDate = ""
> Else
> BlankDate = datInputDate
> End If
> End Function
> I'm just hoping there's a simpler more universal way to do it, either on the
> Access side or the SQL Server 2005 side.
> Thanks!
> Michael
>
>

How do I display SQL Server 1/1/1900 dates as blank in Access?

Is there an easy way to tell Access (2000 or 2003) to display the "1/1/1900"
that SQL Server 2005 considers a blank date as blank rather than as
"1/1/1900"? I know I can create a function like that shown below to do it.
Public Function BlankDate(datInputDate)
If datInputDate < #1/2/1900# Then
BlankDate = ""
Else
BlankDate = datInputDate
End If
End Function
I'm just hoping there's a simpler more universal way to do it, either on the
Access side or the SQL Server 2005 side.
Thanks!
MichaelI thought that blank date was null.
You could try in your query using:
nullif(FieldName, '1/1/1900') as FieldName
Note that this will not be updatable.
--
Russel Loski, MCSD.Net
"Michael Nagan" wrote:

> Is there an easy way to tell Access (2000 or 2003) to display the "1/1/190
0"
> that SQL Server 2005 considers a blank date as blank rather than as
> "1/1/1900"? I know I can create a function like that shown below to do it
.
> Public Function BlankDate(datInputDate)
> If datInputDate < #1/2/1900# Then
> BlankDate = ""
> Else
> BlankDate = datInputDate
> End If
> End Function
> I'm just hoping there's a simpler more universal way to do it, either on t
he
> Access side or the SQL Server 2005 side.
> Thanks!
> Michael
>
>

How do I determine the backup date of a restored Database..?

Hi All,

I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...

For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...

Any help will be much appreciated.. Thanks in advance.
- AravinQuery Builder (querybuilder@.gmail.com) writes:

Quote:

Originally Posted by

I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...
>
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
>
Any help will be much appreciated.. Thanks in advance.

If the backups were taken on the same server, you may be able to dig
this out from the tables in msdb. You would have to start with
restorehistory and go backwards from there. I'm offering a query,
because I have worked very little with these tables myself. But they
are documented in Books Online, althoughly fairly briefly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- Aravin
Hello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegro ups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>> Hi All,
>> I am in a delima here. I have to a few backups restored from a set of
>> backup file. The backup files have been removed from the drive because
>> of some storage constrains...
>> For some data comparison reasons, I need to figure out the actual date
>> of these backup files that is used to restore these databases. Is
>> there any system table I can query to figure the actual backup times
>> of these databases...
>> Any help will be much appreciated.. Thanks in advance.
>> - Aravin
>