Converting to Date

  • Thread starter Thread starter Atley
  • Start date Start date
A

Atley

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.
 
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.
 
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
, in particular
http://www.catb.org/~esr/faqs/smart-questions.html#forum
 
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);
 
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
 
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
 
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) )
 
Back
Top