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: