Friday, February 10, 2012

Convert Char Value to Datetime Value

I need to convert a char value to datetime value. Example from 71004 to
7/10/04; I already tried to use the convert function and the cast function
but none of them work.
ThanksDatetime values are stored in a proprietary format in SQL Server.
I would suggest a two part scheme to convert that value... Step 1 is to
parse it and put the hyphens or slashes in to the string. Once that is
done, you can use the convert command to make it a datetime stamp.
Step 1: Using string manipulation, change 71004 to 07-10-04
Step 2: Convert '07-01-04' to datetime.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> I need to convert a char value to datetime value. Example from 71004 to
> 7/10/04; I already tried to use the convert function and the cast function
> but none of them work.
> Thanks
>|||If you don't use 6-char representation of date, you can have problems with
those dates, where day<13 (21104 is 2.11.04 or 21.1.04?).
On MS SQL you can use convert(datetime,'02.11.04',104) to convert date
from/to different formats. For ddmmyy format I don't know it by head, it's
probably convert(datetime,'021104',112).
Vlastik
"sanvaces" <sanvaces@.discussions.microsoft.com> pí¹e v diskusním pøíspìvku
news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> I need to convert a char value to datetime value. Example from 71004 to
> 7/10/04; I already tried to use the convert function and the cast function
> but none of them work.
> Thanks
>|||Sorry, of course day<10.
When string representation is ddmmyy, you can use also
substring(yourdate,1,2)+'/'+substring(yourdate,3,2)+'/'+substring(yourdate,5
,2).
Vlastik
> If you don't use 6-char representation of date, you can have problems with
> those dates, where day<13 (21104 is 2.11.04 or 21.1.04?).
> On MS SQL you can use convert(datetime,'02.11.04',104) to convert date
> from/to different formats. For ddmmyy format I don't know it by head, it's
> probably convert(datetime,'021104',112).
> Vlastik
> "sanvaces" <sanvaces@.discussions.microsoft.com> pí¹e v diskusním pøíspìvku
> news:976BC321-FDB3-4F47-918F-7BD6DBD51CD2@.microsoft.com...
> > I need to convert a char value to datetime value. Example from 71004 to
> > 7/10/04; I already tried to use the convert function and the cast
function
> > but none of them work.
> >
> > Thanks
> >
>

No comments:

Post a Comment