Varying Length Date

Posted on 2005-04-26
Last Modified: 2010-03-19
I'm trying to extract a date from a varying length string:

DECLARE sData as varchar(100)

Set sData = 'Some Data 20050118 0'
--can also be:
Set sData = 'Some Data 20050118 25643.98'

20050118 is what I'm trying to extract.

Sometimes there's a total at the far right, sometimes, it's just a zero. But the only thing that is certain is that between the date and total there's a blank. Any ideas?


Question by:Trancedified
    LVL 1

    Author Comment


    Forgot the @ sign:

    DECLARE @sData as varchar(100)

    Set @sData = 'Some Data 20050118 0'
    --can also be:
    Set @sData = 'Some Data 20050118 25643.98'
    LVL 28

    Accepted Solution

    declare @sdata varchar(200)
    declare @date VARCHAR(8)

    declare @startpos int
    declare @endpos int

    Set @sdata = 'Some Data 20050118 123.00'
    set @endpos = len(@sdata) - charindex(' ', reverse(@sdata))
    set @sdata = substring(@sdata, 1, @endpos)
    set @sdata = substring(@sdata, len(@sdata) - charindex(' ', reverse(@sdata)) + 2, 8)
    select @sdata
    LVL 1

    Author Comment

    Right on, Thanks!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now