Date Format

Hi everyone.

i have a big database with many stored procedures. i need way that allow me to get date from sql server 2000 by two format(MM/DD/YYYY, YYYY/MM/DD). and if i can do that with minimum effort. i wondering if i can change the come data from sql by using any objects.

any help please.

You could use SET DATEFORMAT. For example:

Code Snippet

SET DATEFORMAT mdy;

|||

yah thanks. i want to change it for all stored procedures from one place. and i want to send the format that i need to return like this

<DFormat>MM/DD/YYYY</DFormat> or <DFormat>YYYY/MM/DD</DFormat>

dont need to send it by each call how i can determine it from one place.

|||

As we see in Books Online the sintax is :

SET DATEFORMAT {format | @.format_var}

so, @.format_var can get value from a table and with your application you can modify dynamically the value in the record of table

|||

ok thanks but i need change it from one place for whole date return from sql. i have many procedures and the change is big.

if i can change it from one place applying to all procedures.

|||

Nope. It is not possible. SQL Server by default return the date values in "YYYY-MM-DD hh:mis.ms". You can't override this format.The retriving format only changed once you convert the datetime values into character string using convert/cast.

You have to do something in your UI.