SQLTeam.com | Weblogs | Forums

Change the date format


#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)

#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


#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.