|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Formatting and returning Time from TextI'm importing a time field as datatype - char, from a text file to prevent
the DTS package from erring - for some reason, if I import it into a DateTime field it gets an error. So bringing it in as char has worked helpe aleveate this problem. However, when I look through the records, I see no 'visible' errors in the Time field. Suffice it to say that I have continued to bring in records this way just for safety. I want to be able create a view to recognize the time field as actual time so that I can sort it appropriately. I know that Cast(Convert) -- handles a complete datetime field. What is the function to manipulate a string into a precise time format recognized as time? You could select from the table using a convert with style 108 to show
HH:MM:SS Select convert(varchar(50), CharColumnName, 108) or you could use a datapart function as well SELECT DATEPART(HH, CharColumnName), DATEPART(MI, CharColumnName), DATEPART(SS, CharColumnName) -- Show quote/* Warren Brunk - MCITP - SQL 2005, MCDBA */ "jonefer" <jone***@discussions.microsoft.com> wrote in message news:AC5DA0AC-6B6F-4F9A-B4CC-6B7BDBE5BFA3@microsoft.com... > I'm importing a time field as datatype - char, from a text file to prevent > the DTS package from erring - for some reason, if I import it into a > DateTime > field it gets an error. So bringing it in as char has worked helpe > aleveate > this problem. > > However, when I look through the records, I see no 'visible' errors in the > Time field. Suffice it to say that I have continued to bring in records > this > way just for safety. > > I want to be able create a view to recognize the time field as actual time > so that I can sort it appropriately. I know that Cast(Convert) -- handles > a > complete datetime field. What is the function to manipulate a string into > a > precise time format recognized as time? I tried both examples:
the first example still sorts as text i.e. 1:00 PM comes before 11:00 AM the datepart example - doesn't work the way you have it listed - please spell it out for me should I contain the whole thing within parenthesis as an alias as follows: Select (DatePart(HH, CharColumnName), DatePart(MI, CharColumnName)) as TimeOnly so far it didn't work... will what I wrote above work , if I do it correctly? - avoid the seconds - I only need the PM or AM part = example 1:45 PM This topic isn't covered in an obvious manner either in help - or what I could find in google, so you are helping me tremendously. Thanks. Show quote "Warren Brunk" wrote: > You could select from the table using a convert with style 108 to show > HH:MM:SS > > Select convert(varchar(50), CharColumnName, 108) > > or you could use a datapart function as well > > SELECT DATEPART(HH, CharColumnName), DATEPART(MI, CharColumnName), > DATEPART(SS, CharColumnName) > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > */ > > > "jonefer" <jone***@discussions.microsoft.com> wrote in message > news:AC5DA0AC-6B6F-4F9A-B4CC-6B7BDBE5BFA3@microsoft.com... > > I'm importing a time field as datatype - char, from a text file to prevent > > the DTS package from erring - for some reason, if I import it into a > > DateTime > > field it gets an error. So bringing it in as char has worked helpe > > aleveate > > this problem. > > > > However, when I look through the records, I see no 'visible' errors in the > > Time field. Suffice it to say that I have continued to bring in records > > this > > way just for safety. > > > > I want to be able create a view to recognize the time field as actual time > > so that I can sort it appropriately. I know that Cast(Convert) -- handles > > a > > complete datetime field. What is the function to manipulate a string into > > a > > precise time format recognized as time? > > > You will probably be best served by adding a datetime column to the table
(after the import if necessary), and then using UPDATE, change that datetime column using a algorithm similar to the one Warren provided. Then your display and ordering issues are easier to grasp and handle. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "jonefer" <jone***@discussions.microsoft.com> wrote in message news:DDC8A490-F777-4E3F-85D9-7226D1B52C7B@microsoft.com... >I tried both examples: > the first example still sorts as text i.e. 1:00 PM comes before 11:00 AM > > the datepart example - doesn't work the way you have it listed - please > spell it out for me should I contain the whole thing within parenthesis > as > an alias as follows: > Select (DatePart(HH, CharColumnName), DatePart(MI, CharColumnName)) as > TimeOnly > > so far it didn't work... will what I wrote above work , if I do it > correctly? - avoid the seconds - I only need the PM or AM part = example > 1:45 PM > > This topic isn't covered in an obvious manner either in help - or what I > could find in google, so you are helping me tremendously. Thanks. > > > "Warren Brunk" wrote: > >> You could select from the table using a convert with style 108 to show >> HH:MM:SS >> >> Select convert(varchar(50), CharColumnName, 108) >> >> or you could use a datapart function as well >> >> SELECT DATEPART(HH, CharColumnName), DATEPART(MI, CharColumnName), >> DATEPART(SS, CharColumnName) >> >> -- >> /* >> Warren Brunk - MCITP - SQL 2005, MCDBA >> */ >> >> >> "jonefer" <jone***@discussions.microsoft.com> wrote in message >> news:AC5DA0AC-6B6F-4F9A-B4CC-6B7BDBE5BFA3@microsoft.com... >> > I'm importing a time field as datatype - char, from a text file to >> > prevent >> > the DTS package from erring - for some reason, if I import it into a >> > DateTime >> > field it gets an error. So bringing it in as char has worked helpe >> > aleveate >> > this problem. >> > >> > However, when I look through the records, I see no 'visible' errors in >> > the >> > Time field. Suffice it to say that I have continued to bring in >> > records >> > this >> > way just for safety. >> > >> > I want to be able create a view to recognize the time field as actual >> > time >> > so that I can sort it appropriately. I know that Cast(Convert) -- >> > handles >> > a >> > complete datetime field. What is the function to manipulate a string >> > into >> > a >> > precise time format recognized as time? >> >> >>  |
|||||||||||||||||||||||
Other interesting topics