Formatting a date in SQL

[Sparkz0629]

Right, so here's the thing, Im currently querying a date from a database, the date comes through in the following format "2006-08-14 00:00:00.0", but i need this date to be converted to "14/08/2006".
I need to do a convert in the sql query to do this, but hopefully without having to create a stored procedure or anything(i want to convert in as im querying it).

Does anyone have any ideas...

Hope i made sense.... lol

 

[FarligOpptreden]

SQL has standard formatting for dates, just Google it a bit. If it doesn't yield the results you want, maybe create a FUNCTION (UDF, i.e. User Defined Function) to call in your selects and use the DATEPART function inside your UDF to format it exactly as you need. The UDF can then possibly be used as follows:

select dbo.FormatDate(DateValue) as FormattedDate from Table

*NOTE: This solution assumes you're using MSSQL. Other RDBMSs might need some tweaking to the solution, but the principal is the same.

 

[Sparkz0629]

SQL has standard formatting for dates, just Google it a bit. If it doesn't yield the results you want, maybe create a FUNCTION to call in your selects and use the DATEPART function inside your function to format it exactly as you need.

Thats the thing, I've been trying google, but you know how full the interwebz can be

Thats why i thought that maybe someone here has had experience with this, and could help...

 

[FarligOpptreden]

I edited my post while you were replying...

 

[DarkStreet]

Right, so here's the thing, Im currently querying a date from a database, the date comes through in the following format "2006-08-14 00:00:00.0", but i need this date to be converted to "14/08/2006".
I need to do a convert in the sql query to do this, but hopefully without having to create a stored procedure or anything(i want to convert in as im querying it).

Does anyone have any ideas...

Hope i made sense.... lol

What kind of database are you using? MS SQL, MySQL, etc.

 

[crazy_cat]

MSSQL functions to the rescue.....

Use convert

select [B]convert[/B](varchar(8), dateColumn, 3) from yourTable

Refer to MSDN for more info

 

[xumwun]

Oracle has to_char, mysql has date_format
That is assuming this is not a text field but a date time field

http://www.ispirer.com/doc/sqlways39/Output/SQLWays-1-076.html

 

[Sparkz0629]

What kind of database are you using? MS SQL, MySQL, etc.

Its a sybase database....

Guys, really sorry to mess you around, but the date format i need to get out is "YYYY/MM/DD"...... frikken spec is so non-descriptive....

 

[Sparkz0629]

MSSQL functions to the rescue.....

Use convert

select [B]convert[/B](varchar(8), dateColumn, 3) from yourTable

Refer to MSDN for more info

Oh, and this function worked.... but just wrong format.... so MSSQL fuctions seem to work

 

[Sparkz0629]

Lol.... thanks for all the help, and thank for putting my on the right track... (MSSQL functions).... the function needed is SELECT CONVERT(VARCHAR(10), datefield, 111) AS field label

 

[FarligOpptreden]

I normally don't do this, but have you even searched? Because that answer was the first link... Maybe you should check out my guide.

...was my first response as well... I guess we just grew up in the post-friendly-forum-members era and were FORCED to search for everything - possibly even buy a book on the subject.

 

[guest2013-1]

http://www.acidrazor.com/blog/date-defaults-what-every-programmer-should-know/

There I explain the all important dd-MMM-yyyy date format and why you should use it. It's also very straight forward converting it to that format in SQL. And you, your users and your application will never be confused about a date ever again, regardless of timezone settings on the server or database default setup

 

[nephelyn]

Just another take on this, if you have written an app that displays the value it should be formated there and not in the query. If you using a query tool directly this is acceptable.

 

[guest2013-1]

Just another take on this, if you have written an app that displays the value it should be formated there and not in the query. If you using a query tool directly this is acceptable.

I found it's easier reading the date out of the database directly when doing the SQL statement, and when you save a date, to save it in the format I talk about in my blog post.

The reason for that is so that you don't have to deal with the restrictions of the programming language you're writing this in, and it stays standard as (generally) SQL is a standardized language by itself. The only thing you'd have to do is get your code to, when saving dates, to send it to the sql statement (or stored procedure) in the dd-MMM-yyyy format. But this shouldn't be difficult at all and usually don't leverage off of any conversion that might need to happen within the language you choose to write your application in.

I genuinely believe this is the best way to approach any "date" stored (or going to be stored) in a database because it sets it (and gets it) in a universally recognized format. Regardless if your date format is dd/mm/yyyy or mm/dd/yyyy or yyyy/mm/dd or written in some chinese/mandarin type... and you could always take this universal date and convert it to the latter very easily and "pain free", so long as you remember to read/write in that format.

No machine or no human will think that 4-Apr-2010 is actually 3-Mar-2010 (03/04/2010 vs 04/03/2010)