I have a field in a database that contains dates in the following format:

20040205

It is a text field and I need to convert it to a date so I can compare and
control a dataset.

Any suggestions would be greatly appreciated... I have tried to use
GetDate(20040205), but it just gives me an error...

The unfortunate part of this is that the value is simply a text value and
there is no easy way to convert it to a date. Is it possible to change the
data type in the DB to a date type? This would greatly simplify your life.

If not, then you will need to look into splitting the string value into its
three parts, year, month and day, and then inserting them into a date type
somehow.

--
Gerry O'Brien
Visual Developer .NET MVP



"Atley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a field in a database that contains dates in the following format:
>
> 20040205
>
> It is a text field and I need to convert it to a date so I can compare and
> control a dataset.
>
> Any suggestions would be greatly appreciated... I have tried to use
> GetDate(20040205), but it just gives me an error...
>
>

If it's in a known form everytime (yyyymmdd) then just parse the string and
then build a DateTime struct yourself.

--
Tim Wilson
..Net Compact Framework MVP
{cf147fdf-893d-4a88-b258-22f68a3dbc6a}
"Atley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a field in a database that contains dates in the following format:
>
> 20040205
>
> It is a text field and I need to convert it to a date so I can compare and
> control a dataset.
>
> Any suggestions would be greatly appreciated... I have tried to use
> GetDate(20040205), but it just gives me an error...
>
>

This syntax works:

select convert(datetime, '20040205')

As Gerry O'Brien said, it would be better to convert the column to datetime
format if possible.


"Atley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a field in a database that contains dates in the following format:
>
> 20040205
>
> It is a text field and I need to convert it to a date so I can compare and
> control a dataset.
>
> Any suggestions would be greatly appreciated... I have tried to use
> GetDate(20040205), but it just gives me an error...
>
>

Do not cross-post. Ever. What does this have to do with .datamining???

To parse this date string use DateTime.ParseExact(string, string,
IFormatProvider):

Dim dt as DateTime = DateTime.ParseExact("20040205", "yyyyMMdd", Nothing)
If you have several possible formats you can use an overload that takes an
array of format strings as a second parameter.


For great netiquette suggestions see
http://www.catb.org/~esr/faqs/smart-questions.html, in particular
http://www.catb.org/~esr/faqs/smart-...ons.html#forum



"Atley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a field in a database that contains dates in the following format:
>
> 20040205
>
> It is a text field and I need to convert it to a date so I can compare and
> control a dataset.
>
> Any suggestions would be greatly appreciated... I have tried to use
> GetDate(20040205), but it just gives me an error...
>
>

Atley,

Assuming that your date is in the format YYYYMMDD, you can use something
like this (or the VB.Net equivalent):

string s = "20040204";
string s2 = s.Substring(4, 2)+"/"+s.Substring(6,2)+"/"+s.Substring(0,4);
DateTime d = DateTime.Parse(s2);

--
Ginny Caughey
..Net Compact Framework MVP

"Atley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a field in a database that contains dates in the following format:
>
> 20040205
>
> It is a text field and I need to convert it to a date so I can compare and
> control a dataset.
>
> Any suggestions would be greatly appreciated... I have tried to use
> GetDate(20040205), but it just gives me an error...
>
>

I think crossposting in relevant groups is acceptable, in fact I prefer it.
For example, a crosspost to the CF and PPC groups on a CF/PPC issue would be
warranted, and an answer in one group will show up in the other.

Now how this specific question has anything to do with sqlserver or
datamining is beyond me, so this is an example of a bad use of crossposting
that I also do not condone.

--
Chris Tacke, eMVP
Co-Founder and Advisory Board Member
www.OpenNETCF.org
---
Windows CE Product Manager
Applied Data Systems
www.applieddata.net


"Alex Feinman [MVP]" <(E-Mail Removed)> wrote in message
news:OSQB%(E-Mail Removed)...
> Do not cross-post. Ever. What does this have to do with .datamining???
>
> To parse this date string use DateTime.ParseExact(string, string,
> IFormatProvider):
>
> Dim dt as DateTime = DateTime.ParseExact("20040205", "yyyyMMdd", Nothing)
> If you have several possible formats you can use an overload that takes an
> array of format strings as a second parameter.
>
>
> For great netiquette suggestions see
> http://www.catb.org/~esr/faqs/smart-questions.html, in particular
> http://www.catb.org/~esr/faqs/smart-...ons.html#forum
>
>
>
> "Atley" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a field in a database that contains dates in the following
format:
> >
> > 20040205
> >
> > It is a text field and I need to convert it to a date so I can compare
and
> > control a dataset.
> >
> > Any suggestions would be greatly appreciated... I have tried to use
> > GetDate(20040205), but it just gives me an error...
> >
> >
>
>

It's possible to use custom format for parsing DateTime:

string[] formats = new string[] { "yyyyMMdd" /* add more formats as needed
*/};

DateTime date = DateTime.ParseExact (date_string, formats,
DateTimeFormatInfo.InvariantInfo,
DateTimeStyles.AllowLeadingWhite|DateTimeStyles.AllowTrailingWhite);

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
> From: "Ginny Caughey [MVP]" <(E-Mail Removed)>
> References: <(E-Mail Removed)>
> Subject: Re: Converting to Date
> Date: Tue, 9 Mar 2004 15:49:42 -0500
> Lines: 28
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
> Message-ID: <(E-Mail Removed)>
> Newsgroups:
microsoft.public.dotnet.framework.compactframework,microsoft.public.dotnet.l
anguages.vb,microsoft.public.sqlserver.ce,microsoft.public.sqlserver.clients
,microsoft.public.sqlserver.datamining,microsoft.public.sqlserver.programmin
g
> NNTP-Posting-Host: rrcs-midsouth-24-199-182-238.biz.rr.com 24.199.182.238
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:187531
microsoft.public.sqlserver.ce:11595
microsoft.public.sqlserver.clients:27301
microsoft.public.sqlserver.datamining:4779
microsoft.public.sqlserver.programming:429399
microsoft.public.dotnet.framework.compactframework:48110
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
>
> Atley,
>
> Assuming that your date is in the format YYYYMMDD, you can use something
> like this (or the VB.Net equivalent):
>
> string s = "20040204";
> string s2 = s.Substring(4, 2)+"/"+s.Substring(6,2)+"/"+s.Substring(0,4);
> DateTime d = DateTime.Parse(s2);
>
> --
> Ginny Caughey
> .Net Compact Framework MVP
>
> "Atley" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a field in a database that contains dates in the following
format:
> >
> > 20040205
> >
> > It is a text field and I need to convert it to a date so I can compare
and
> > control a dataset.
> >
> > Any suggestions would be greatly appreciated... I have tried to use
> > GetDate(20040205), but it just gives me an error...
> >
> >
>
>
>

Well if hes talking about a string....... at worst case, It's elementry
string parsing...... sounds like he wants do it at the SQL level.

(which makes it relevent to sqlserver)

He does need to be more specific!

Mo



"Chris Tacke, eMVP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I think crossposting in relevant groups is acceptable, in fact I prefer
it.
> For example, a crosspost to the CF and PPC groups on a CF/PPC issue would
be
> warranted, and an answer in one group will show up in the other.
>
> Now how this specific question has anything to do with sqlserver or
> datamining is beyond me, so this is an example of a bad use of
crossposting
> that I also do not condone.
>
> --
> Chris Tacke, eMVP
> Co-Founder and Advisory Board Member
> www.OpenNETCF.org
> ---
> Windows CE Product Manager
> Applied Data Systems
> www.applieddata.net
>
>
> "Alex Feinman [MVP]" <(E-Mail Removed)> wrote in message
> news:OSQB%(E-Mail Removed)...
> > Do not cross-post. Ever. What does this have to do with .datamining???
> >
> > To parse this date string use DateTime.ParseExact(string, string,
> > IFormatProvider):
> >
> > Dim dt as DateTime = DateTime.ParseExact("20040205", "yyyyMMdd",
Nothing)
> > If you have several possible formats you can use an overload that takes
an
> > array of format strings as a second parameter.
> >
> >
> > For great netiquette suggestions see
> > http://www.catb.org/~esr/faqs/smart-questions.html, in particular
> > http://www.catb.org/~esr/faqs/smart-...ons.html#forum
> >
> >
> >
> > "Atley" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > I have a field in a database that contains dates in the following
> format:
> > >
> > > 20040205
> > >
> > > It is a text field and I need to convert it to a date so I can compare
> and
> > > control a dataset.
> > >
> > > Any suggestions would be greatly appreciated... I have tried to use
> > > GetDate(20040205), but it just gives me an error...
> > >
> > >
> >
> >
>
>

This question has been answered...sort of.

Parsing data is easy...you just need to know what you are parsing. Make sure that you know what format the data is in. Is it YYYYMMDD or YYYYDDMM?

If you don't want to use convert (the solution that has already been given), here is a way that you can do the conversion via the SUBSTRING function.

example:
DECLARE @YourStringDate char(8)
SET @YourStringDate = '20040205'
SELECT SUBSTRING(@YourStringDate,1,4) AS TheYear,
SUBSTRING(@YourStringDate,5,2) AS TheMonth,
SUBSTRING(@YourStringDate,7,2) AS TheDate

SELECT SUBSTRING(@YourStringDate,5,2) + '/' + SUBSTRING(@YourStringDate,7,2) + '/' + SUBSTRING(@YourStringDate,1,4)
SELECT SUBSTRING(@YourStringDate,7,2) + '/' + SUBSTRING(@YourStringDate,5,2) + '/' + SUBSTRING(@YourStringDate,1,4)


SELECT CONVERT(datetime,SUBSTRING(@YourStringDate,5,2) + '/' + SUBSTRING(@YourStringDate,7,2) + '/' + SUBSTRING(@YourStringDate,1,4) )
SELECT CONVERT(datetime,SUBSTRING(@YourStringDate,7,2) + '/' + SUBSTRING(@YourStringDate,5,2) + '/' + SUBSTRING(@YourStringDate,1,4) )


--
Keith


"Atley" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> I have a field in a database that contains dates in the following format:
>
> 20040205
>
> It is a text field and I need to convert it to a date so I can compare and
> control a dataset.
>
> Any suggestions would be greatly appreciated... I have tried to use
> GetDate(20040205), but it just gives me an error...
>
>