Convert

I want to do this

SELECT TOP 100 PERCENT CONVERT(smalldatetime, DOCDATE, 105) AS DOCDATE
FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO

It is returning a date as 13/12/2003 ?

Any Ideas ?

Michaelwhat result are u expecting?|||I expect 13-12-2003|||select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))|||select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO|||Originally posted by harshal_in
select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))

woops i get 13-12-347 ??|||oh sorry!!
select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(yy,docdate))FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO|||Originally posted by harshal_in
oh sorry!!
select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(yy,docdate))FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO

Thanks very much works a treat

Very much appreciate your Help|||Grievem,

Your query is (was) actually returning a datetime datatype which was being displayed in a different format than you desired.

Ideally, you should leave it be, and then let whatever interface you use do the formatting you want. This is because if you convert it to a character field you will have difficulty sorting or grouping on the results.

Let SQL Server do the calculations and the data retrieval, which it is best at, and let the interface do the formatting, because that is what it is best at.|||blindman's points are good. In your query, it is not neccesary to convert one data type to another. You can do it on your program, i.e., ASP code.|||I do agree with both of u it is not necessary to convert the datatype into varchar as it would affect the sorting and other stuff.The best way to format it is in the front end.
regards,
harshal.|||Originally posted by blindman
Ideally, you should leave it be, and then let whatever interface you use do the formatting you want. This is because if you convert it to a character field you will have difficulty sorting or grouping on the results. this is quite true, insofar as it is good general advice, however, i would like to point out that ISO standard date formats do sort and group perfectly well

furthermore, if you just "let it be", it may come out in a format that you will then have to unstring in your application code to get into the format you prefer!!! this makes the application code dependent on whichever default date format this particular installation uses, and blows you right out of the water if somebody changes the default!!!

my advice: always display dates out of the database in ISO format

also, a comment on this expression:

CONVERT(smalldatetime, DOCDATE, 105)

please note, the style value of the CONVERT function, e.g. 105 in the above example, is used to determine the display format for converting a datetime value to character string, not for helping the database to "unstring" or "interpret" or "decipher" a string value

as far as i know, the database will convert a string into a datetime value depending only on whether it can recognize a valid date value in the string, and not by any helpful "hint" you may be trying to throw its way

at least, that has been my experience, and that is also how i interpret the CAST and CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) documentation:style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types|||"please note, the style value of the CONVERT function, e.g. 105 in the above example, is used to determine the display format for converting a datetime value to character string, not for helping the database to "unstring" or "interpret" or "decipher" a string value"

That's what I used to think as well, but I believe the point was made and demonstrated on this forum a few months back that the convert function will use the style value when deciding whether to interpret "01/02/03" as "Jan 2, 2003", "Feb 1, 2003", or "Feb 3, 2001".

select convert(datetime, '01/02/03', 1)
select convert(datetime, '01/02/03', 3)
select convert(datetime, '01/02/03', 11)

Results:
2003-01-02 00:00:00.000
2003-02-01 00:00:00.000
2001-02-03 00:00:00.000|||whoa

shock and awe

microsoft product actually works better than its documentation

who'd'a thunk it

thanks, blindman