Convert to Date

Question

Post reply

Convert to Date

rachel

SSCommitted

Points: 1626
More actions
April 2, 2002 at 2:29 pm

#78920

I'm trying to extract a substring and convert it to a datetime variable without success. Can someone assist me?
The original string containing the date is <wbMMDDYYYY1.txt>.
Here is what I am trying - and I have tried all different variations of single quotes...
declare @filedate datetime
set @filedate = Right(left(@files, 4), 2) + '/' + Right(left(@files, 6), 2) + '/' +
Right(left(@files, 10), 4)
I know I somehow need to get single quotes at the beginning and end of the equation, but am having no success.
Any help is appreciated!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

petey20 SSCommitted Points: 1741 More actions · Answer 1

Just use a substring function then a convert with a style

Here's from the help:

The style Parameter

The style parameter of CONVERT provides a variety of date display formats when converting datetime data to char or varchar. The number you supply as the style parameter determines how the datetime data is displayed. The year can be displayed in either two or four digits. By default, SQL Server supplies a two-digit year. To display a four-digit year including the century (yyyy), even if the year data was stored by using a two-digit year format, add 100 to a style value to get a four-place year.

This example shows CONVERT with the style parameter:

SELECT CONVERT(char(12), GETDATE(), 3)

This statement converts the current date to style 3, dd/mm/yy.

So I'd do a set @filedate = CONVERT(char(12), substring(@files,2,8),103)

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Try this

DECLARE @filedate VARCHAR(10)

SET @filedate = SUBSTRING(@files,3,2) + '/' + SUBSTRING(@files,5,2) + '/' + SUBSTRING(@files,7,4)

And test

PRINT @filedate

You should get MM/DD/YYYY, if not what do you get?

If you do then use CAST(@filedate AS DATETIME) and set a datetime variable = to that.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

rachel SSCommitted Points: 1626 More actions · Answer 3

Thanks - I think that will fix it. I was trying to convert the same variable from a varchar to datetime, instead of casting the varchar into a different datetime variable.

rachel SSCommitted Points: 1626 More actions · Answer 4

Ok, I get the MM/DD/YYYY

but then

set @datevar = cast(@filedate as datetime) gives me a conversion error.

@filedate is a varchar(10)

@datevar is datetime

and @filedate holds 01/11/2002

Thanks!!

Antares686 SSC Guru Points: 125444 More actions · Answer 5

What are you servers regional settings for dates and what language is the SQL Server using. If not MM/DD/YYYY as default it may not understand that format. Add

SET DATEFORMAT mdy

to the begining of the code should get around. Or build your dates based on your settings.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)