I need to convert a txt field to SQL datetime that looks like the following:
199902250215
So it only has the yyyy/mm/dd hh:mm
SQL does not seem to like that it doesn't have the seconds and miliseconds.
How can I do this?
Thank you!What does it really look like? You show the example without slashes or
spaces yet the format example does have them. At the least you will need a
space in between the days and hours.
Andrew J. Kelly SQL MVP
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:84E1E5E5-3AA0-4264-BE34-64F6FF3CD1B5@.microsoft.com...
>I need to convert a txt field to SQL datetime that looks like the
>following:
> 199902250215
> So it only has the yyyy/mm/dd hh:mm
> SQL does not seem to like that it doesn't have the seconds and
> miliseconds.
> How can I do this?
> Thank you!
>|||Sorry, the data looks exactly like I typed it in (coming from a text field),
not like the one I typed with slashes. Here is what I do to the field when I
import it, but it does not bring the time in, it only updates the year month
and date part of the field.
UPDATE WHJOINTDATA.DBO.FACT_CLAIM
SET WHJOINTDATA.DBO.FACT_CLAIM.policy_date_time =
WHWANGDATA.DBO.WANG_LOSS.pol_date
FROM WHWANGDATA.DBO.WANG_LOSS
WHERE ISDATE(WHWANGDATA.DBO.WANG_LOSS.pol_date) <> 0
AND WHJOINTDATA.DBO.FACT_CLAIM.POLICY_NUMBER =
WHWANGDATA.DBO.WANG_LOSS.LP_NUMBER
"Andrew J. Kelly" wrote:
> What does it really look like? You show the example without slashes or
> spaces yet the format example does have them. At the least you will need
a
> space in between the days and hours.
> --
> Andrew J. Kelly SQL MVP
>
> "Patrice" <Patrice@.discussions.microsoft.com> wrote in message
> news:84E1E5E5-3AA0-4264-BE34-64F6FF3CD1B5@.microsoft.com...
>
>|||What you need to do is add a space and a : to make it work with a CONVERT
such as this:
select CONVERT(DATETIME,LEFT(@.pol_date,8) + ' ' + SUBSTRING(@.pol_date,9,2) +
':' + SUBSTRING(@.pol_date,11,2))
Andrew J. Kelly SQL MVP
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:30741AD2-4355-42DB-8623-81A1728BFFA9@.microsoft.com...
> Sorry, the data looks exactly like I typed it in (coming from a text
> field),
> not like the one I typed with slashes. Here is what I do to the field when
> I
> import it, but it does not bring the time in, it only updates the year
> month
> and date part of the field.
> UPDATE WHJOINTDATA.DBO.FACT_CLAIM
> SET WHJOINTDATA.DBO.FACT_CLAIM.policy_date_time =
> WHWANGDATA.DBO.WANG_LOSS.pol_date
> FROM WHWANGDATA.DBO.WANG_LOSS
> WHERE ISDATE(WHWANGDATA.DBO.WANG_LOSS.pol_date) <> 0
> AND WHJOINTDATA.DBO.FACT_CLAIM.POLICY_NUMBER =
> WHWANGDATA.DBO.WANG_LOSS.LP_NUMBER
> "Andrew J. Kelly" wrote:
>
No comments:
Post a Comment