get date in the right format 1

[mrdance]

When I select date I get a result which says: 1st of January 1995 in my language. What if I just want 1995-01-01?

Thankful for any help!

// Henrik

 

[rajeevnandanmishra]

hi,
i think, the date format required by you "yyyy-mm-dd" is not generated from SQL. Though "yyyy/mm/dd" can be generated.
You can use CONVERT(CHAR(10),getDate(),111). this will return you "2001/06/19" for today.
You can see more date formats in SQL BOL for "CONVERT" function help.

 

[tlbroadbent]

There is no style you can choose to get yyyy-mm-dd format for dates using the CONVERT function. However, using the REPALCE function in conjunction with the CONVERT function, you can achieve what you want.

Select replace(convert(char(10), getdate(),111),'/','-') Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[mrdance]

Thanks, but I am using PHP and the date field, an_datum, is now empty using this select;

$result=mssql_query("SELECT TOP 20 CONVERT(char(20),an_datum,111),an_isin,an_signaltyp,an_kommentar,an_kalla FROM tbl_analys ORDER BY an_datum DESC", $db);

Any suggestions?

 

[tlbroadbent]

You need to assign a name (ALIAS) to the converted column. In SQL Server, you can use the same name as the column.

$result=mssql_query("SELECT TOP 20 REPLACE(CONVERT(char(20),an_datum,111), '/','-') As an_datum , an_isin, an_signaltyp, an_kommentar, an_kalla FROM tbl_analys ORDER BY an_datum DESC", $db);
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[mrdance]

Cool, it works! Thank you all for your answers!

tlbroadbent - an extra thanks for your REPLACE

// jp