Sql convert date format

Question

I want to convert dateformat from mm/dd/yyyy to yyyy/mm/dd. I want the output in datetime format. I tried this

convert(datetime,convert(varchar,getdate(),111),123)

but doesn't work. The error is "explicit conversion to datetime not available" What is the best way to solve this problem? I'm using Sybase.

Solution 1

That won't work. The DATETIME data type has its own format that is really the amount of time that has passed since a fixed reference date; if you ask for a DATETIME it will always be returned according to that format.

How it is displayed to an end user is a function of the client. You can use CONVERT to convert it to a string and specify a format for how it is displayed in the string, but then you're returning a string, not a DATETIME. You can return it as a DATETIME (which has no inherent display format), and then it is up to the client application or OS to define how it is formatted for display. In client applications you also typically have formatting functions that display a date/time according to a format you specify. And if you haven't specified it explicitly in an application, then the display of the date/time will typically be defined by the localization settings in the OS.

Basically, there is a difference between the data type - DATETIME - and its representation to end users.

Solution 2

Try this

select convert(varchar,CAST('12/11/2010' as DateTime),111)

Solution 3

Formatting is something that should be done in the presentation tier not the data tier. However, most vendors, like Sybase, provide the ability to do rudimentary formatting:

Select Cast( Year(GetDate()) As char(4)  )
    + '/' + Right( '00' + Cast( Month(GetDate()) As varchar(2) ), 2 )
    + '/' + Right( '00' + Cast( Day(GetDate()) As varchar(2) ), 2 ) 

Comments

[Marek Kwiendacz]

what kind of server? Mssql, postgresql, mysql, oracle etc. for date time it is important information.