• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

SQL Serving converting substring to date

SQL Server 2008.
I have a file name that contains date information that I need to load into a date field. To get the date information I'm using a substring. I am not able to convert the substring to a date, please help.

File Name: XXXX_XXXXRN_FLAT10_XXX_20131230_0731.DAT

substring: SUBSTRING(FILE_NM,24,4)+'-'+SUBSTRING(FILE_NM,28,2)+'-'+SUBSTRING(FILE_NM,30,2)+' 00:00:00.000'

Returns: 2013-12-30 00:00:00.000

I am not able to update the 2013-12-30 00:00:00.000 to a date field. I have tried CAST and CONVERT on the substring with no luck.
0
SharonBernal
Asked:
SharonBernal
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
Use a style number when converting (here's a list)

XXXX_XXXXRN_FLAT10_XXX_20131230_0731.DAT
                                                  ^^^^^^^^

there happens to be a style for YYYYMMDD so it can be simplified too

     convert(date,substring(file_nm,24,8),112)
or
     convert(datetime,substring(file_nm,24,8),112)

---
ps: the safest possible date string to use in SQL Server is YYYYMMDD
0
 
SharonBernalAuthor Commented:
The substring is still not converted to a date.

I get this message:
Conversion failed when converting date and/or time from character string.
0
 
SharonBernalAuthor Commented:
Thanks for pointing me in the right direction. I've figured it out.
0
 
PortletPaulCommented:
What was the problem?
(I did actually test what I proposed and it worked without issue)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now