Date conversion

[jrprogr]

Please help me in date conversion with MMDDYYY..

select convert(varchar,'2006254',101)

 

[AlexCuse]

I'm not sure that that is an acceptable date format. I cannot find an option for it. You could try something like this though?

select replace(convert(varchar,getdate(),101), '/', '')

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jrprogr]

select convert(varchar,'2006254',101)

I want the below ouput for the value 2006254

01/25/2006

 

[bborissov]

You can't get 01/25/2006 from 2006254 )))))

SET DATEFORMAT MDY
SELECT (convert(varchar(20), CAST('20060125' as datetime),101))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

how are you storing your dates?

I am having trouble understanding how '2006254' should convert to 1/25/06. 4/25/06 would make a slight bit of sense.

Let me know,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jrprogr]

The values are storing in Julian format.
Instead converting to dateformat.
Can i validate rejecting the data in julian format....

 

[AlexCuse]

Define 'validate rejecting the data in Julian format'

What you could do (if this is for an insert), is add

where isdate(date_field) = 1

This will kcik out anything not convertible to a date.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLBill]

The Julian date for 25 January is NOT 254. It's 025.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Check out THREAD183-1190041. While the Julian date format is different than yours (2 digit year vice your 4 digit year) the solution should be similar.

-SQLBill

Posting advice: FAQ481-4875