Change the date format

Transact-SQL
1

Date format decDate - decimal(9,0) Ex:: 31102015
need to change to this format " MM-DD-YYYY"

thanks

2 
SELECT CONVERT(DATE,CONVERT(VARCHAR, decDate), 102) decDate
3

thanks for the reply
i am getting this error

Msg 243, Level 16, State 1, Line 5
Type DATE is not a defined system type.

4

You must be using 2005 or older try datetime, you can remove the time part if needs be.

5

dear tried already i am getting below error

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting datetime from character string.

6

actually i have to use where condition on this field
i need to extract 2 years of data from date 01122013(01-dec-2013) to 01-dec-2015(01-dec-2015).

7

Odd, try:

select convert(varchar, convert(datetime, convert(varchar(10), [decDate])), 105)
1 Like
8

same error

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting datetime from character string.

9

What version of SQL Server are you using?

10

2005 sqlserver

11

What happens with you run?

Does this give you ddmmyyyy?

12

yes it gives me ddmmyyyy
actually i have to use where condition on this fieldi need to extract 2 years of data from date 01122013(01-dec-2013) to 01-dec-2015(01-dec-2015)

13

See if this helps give you some ideas

[code]DECLARE @decDate DECIMAL(9,0) = 31102015;

WITH cte AS (
SELECT CONVERT(VARCHAR(20), @decDate) VDate
)
SELECT CAST(RIGHT(VDate,4) + SUBSTRING(VDate, 3, 2) + LEFT(VDate, 2) AS DATE)
FROM cte;
[/code]
The reason for the cte is so I would not have to have the convert three times

1 Like
14

can i use the below condition in where
and ((right(decdate,6) >= '122013') and (right(decdate,6) <= '112015'))

15

I would not suggest that, however it can be donedeclare @d decimal(9,0) = 31102015; select CASE WHEN RIGHT(@d,6)<= '122013' THEN 1 ELSE 0 END;

16

What you really need to do is convert the column for a really poorly formed integer version of a date to a real datetime and then EVERYTHING gets a whole lot easier. My recommendation would be to add a persisted computed column (so you can index it) that uses a formula to convert that mess to a real datetime.