|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date Parsing using T-SQLI 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 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: Show quoteHide quote > 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 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: Show quoteHide quote > 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 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 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: Show quoteHide quote > 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 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 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: Show quoteHide quote > 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: > > > 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 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 Show quoteHide quote "robboll" <robb***@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: Show quoteHide quote > Convert(datetime, Replace([dkey], ' ',''), 112) > > Tom > > "robboll" <robb***@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 > > 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: Show quoteHide quote > Convert(datetime, Replace([dkey], ' ',''), 112) > > Tom > > "robboll" <robb***@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 > > 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: Show quoteHide quote > 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" <robb***@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 > > > 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: Show quoteHide quote > 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: > > > Convert(datetime, Replace([dkey], ' ',''), 112) > > > > > > Tom > > > > > > "robboll" <robb***@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 > > > >  
Other interesting topics
Tough SQL problem, need expert advice!!!
Advice Requested : Trying to write portable SQL How to add separator blank rows by SQL Query? find the first row of ordered records that sum is less than a cert Comparing dates in one field Obtain values from different tables How can I update the col value using extended stored procedure Dynamic View ALL IN ONE SQL STATEMENT? Insert by Parameter |
|||||||||||||||||||||||
