Date Parsing with DateValue Equivalent

Thanks in Advance,
MS Access has a flexible function called DateValue that will convert a
valid string into a date. Does anyone know a method of parsing ntext
values to detect and convert this strings into dates? The ntext values
might look something like this:
Established in 1974.
Created in Aug 1986 and dedicated on September 29th, 1986.
Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
functions on March 11, 1981 by executive order.
I suspect a combination of patindex search and other functions will do
the trick.
Mark
Napa, CAIf you are using SQL Server 2005, you could write a User Defined Function in
a .NET language that could do this type of parsing for you.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
<xxxdbaxxx@.gmail.com> wrote in message
news:1145893343.427405.182460@.v46g2000cwv.googlegroups.com...
> Thanks in Advance,
> MS Access has a flexible function called DateValue that will convert a
> valid string into a date. Does anyone know a method of parsing ntext
> values to detect and convert this strings into dates? The ntext values
> might look something like this:
> Established in 1974.
> Created in Aug 1986 and dedicated on September 29th, 1986.
> Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
> functions on March 11, 1981 by executive order.
> I suspect a combination of patindex search and other functions will do
> the trick.
> Mark
> Napa, CA
>|||Well, we are still in SQL 2000 Chuck. Any other thoughts?

Date Parsing using T-SQL

I can do this using MS Access 2000 database code.
String called
DKEY: "199306 30"
Using MS Access to convert that to a date the following code works:
DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
Returns the date value: 6/30/1993
How is this done using T-SQL? Any help greatly appreciated!!!
RBollingerIf your datestring is always formatted in that fashion (ie,
"YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
spaces and convert it to a date.
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
HTH,
Stu
robboll wrote:
> I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger|||Convert(datetime, Replace([dkey], ' ',''), 112)
Tom
"robboll" <robboll@.hotmail.com> wrote in message
news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...
>I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger
>|||thanks!
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||I get an error when you trying this. In Access you have to accout for
a - or / or . delimiter between dates. Does the same apply to T-SQL?
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
Stu wrote:
> If your datestring is always formatted in that fashion (ie,
> "YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
> spaces and convert it to a date.
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> HTH,
> Stu
>
> robboll wrote:|||I get an error when trying this. In Access you have to accout for a -
or / or . delimiter between dates. Does the same apply to T-SQL?
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
SUBSTRING(dkey, 1, 4)
provides a readable format, but there are no leading zeros in the day
like there are in the month, and it's still a text string.
convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
robboll wrote:
> I get an error when trying this. In Access you have to accout for a -
> or / or . delimiter between dates. Does the same apply to T-SQL?
> Tom Cooper wrote:|||Okay -- This works. Y'all got me on the right track. Thank you!
CONVERT (smalldatetime, SUBSTRING(dkey, 5, 2) + '/' +
LTRIM(SUBSTRING(dkey, 9, 3)) + '/' + SUBSTRING(dkey, 1, 4))
robboll wrote:
> SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
> SUBSTRING(dkey, 1, 4)
> provides a readable format, but there are no leading zeros in the day
> like there are in the month, and it's still a text string.
> convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
> 2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
>
> robboll wrote:|||What error did you get? In SQL, a format of 20060601 is preferred;
it's unambiguous, and the pattern you provided should have matched
that. The only thing I can think of is that there must be some other
delimiters besides spaces.
I see you found a solution, but was just curious.
robboll wrote:
> I get an error when you trying this. In Access you have to accout for
> a - or / or . delimiter between dates. Does the same apply to T-SQL?
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> Stu wrote:|||For some reason I think the problem had to do with an inconsistent
string value where the month had leading zeros and the days didn't. To
get around this I used an acceptable date delimiter "/". But you're
absolutely correct it will work with the string date in your example.
Unfortunately mine was 2006061.
Stu wrote:
> What error did you get? In SQL, a format of 20060601 is preferred;
> it's unambiguous, and the pattern you provided should have matched
> that. The only thing I can think of is that there must be some other
> delimiters besides spaces.
> I see you found a solution, but was just curious.
> robboll wrote:

date parsing

Hello,

I have a source with two smalldatetime fields, the first field contains 8/1/2006 12:00:00 AM, and the second field contains 8/10/2006 7:57:00 PM.

I would like to have the date from the first field and the time from the second field. No chance of changing the source system to do this for me.

What I have so far works, except the time portion is converted to 19:57:00 instead of 7:57:00 p.m. Any Ideas? My expression is below.

(DT_STR,2,1252)DATEPART("month",FIELD1) + "/" + (DT_STR,2,1252)DATEPART("Day",FIELD1) + "/" + (DT_STR,4,1252)DATEPART("Year",FIELD1) + " " + (DT_STR,2,1252)DATEPART("Hour",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("Minute",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("SS",FIELD2)

Thanks!

Try casting the values as DT_DBTIME or DT_DBDATE.

-Jamie

|||I added a data conversion transform to convert the output from the derived column transform to a database timestamp and it appears to be working. I probably could do all of this in one transformation, but this will work for now. Thanks!|||

You could wrap the cast to DT_DBTIMESTAMP around your whole expression in the derived column to avoid using the data convert downstream.

Mark

|||Thanks. I tried that and it worked great.