Dates formatting in SQL queries 1

[jazerr]

A customer gave me some data and their dates are in this format - 70599. Where 7 is month, 05 is day, and 99 is year. They format is consistnt throughout the data, so an update statement would work. How can I change this to be in 12/15/99 format using SQL statements?

 

[foxdev]

You can probably use CAST along with SUBSTRING to format the date, although the fact that the first number may be one or two digits could get hairy.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[jazerr]

Could you give an example of how the code should look?

 

[TomSark]

-- I assume months and days are always 2 digits
DECLARE @Month varchar(2),
@Day varchar(2),
@Year varchar(2),
@OldDate varchar(30),
@NewDate datetime

SET @OldDate = '070599'
SET @Month = Left( @OldDate , 2 )
SET @Day = Substring( @OldDate , 3, 2 )
SET @Year = Right( @OldDate , 2 )
SET @NewDate = Convert (datetime, @Month + '/' + @Day + '/' + @Year, 1 )

SELECT @NewDate Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[foxdev]

Well, like I said the truly difficult part is figuring out if you should take the first or first and second digits as the month. Your example of '70599' shows that the day is already left-padded, so that is no problem.

Basically, the code would be something like this:

[tt]select cast(left('70599', 1) + "-" +
substring('70599', 2, 2) + "-" +
right('70599', 2) as datetime)[/tt]

But this won't work right for Oct-Dec, so you should wrap it in a CASE or IF statement, as in

[tt]declare @DateChar as char(6)
declare @DateValue as datetime

set @DateChar = '70599' --or your column name
if left(@DateChar,1) = '1'
set @DateValue = cast(left(@DateChar, 2) + "-" +
substring(@DateChar, 3, 2) + "-" +
right(@DateChar, 2) as datetime)

else

set @DateValue = cast(left(@DateChar, 1) + "-" +
substring(@DateChar, 2, 2) + "-" +
right(@DateChar, 2) as datetime)
[/tt]

Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[jazerr]

What I did was this:
select top 10
newdate =
(Case when LEN(MyDate) = 6 then LEFT(MyDate, 2)
WHEN LEN(MyDate) = 5 THEN ('0' + LEFT(MyDate, 1))
END) + '/' +
(reverse(substring(reverse(MyDate), 3, 2))) + '/' +
(right(MyDate, 2))
FROM [Mytable]


This does essentially the same thing as yours, but it wont convert the new value to a datetime...I will try yours.