Changind date formats

[OKI]

Hi.

I had a VB program that was using an SQL Server 6.5 DB. Date format
was dd/mm/yyyy. Now the DB was changed to an SQL Server 7 and the date
format is yyyy/mm/dd.

How can I do for changing date formats in the new DB to the old
format?

I´m searching for a database level solution, not server level.

thanks

[Erland Sommarskog]

OKI (al***********@hotmail.com) writes:

I had a VB program that was using an SQL Server 6.5 DB. Date format
was dd/mm/yyyy. Now the DB was changed to an SQL Server 7 and the date
format is yyyy/mm/dd.

How can I do for changing date formats in the new DB to the old
format?

I´m searching for a database level solution, not server level.

There are not really any date formats in databases in SQL Server. Dates
are stored as binary values, and ideally you program should communicate
with SQL Server using these binary values, and all conversions be
carried out the client, respecting the users regional settings.

If there is a need to control format in SQL Server code, you can use
the convert function, look it up in Books Online under Cast and Convert
for the full details. For instance, if you say:

SELECT convert(char(8), datecol, 112)

You get a datetime value on the form YYYYMMDD. If you say:

SELECT convert(datetime, @stringval, 121)

@stringval is interpreted according to format 121, which if memory
serves is YYYY-MM-DD HH:MM:SS.mmm.

If you just say:

SELECT @dateval = @stringval

there is an implicit conversion, which is carried out according to the
current datetime and language settings for the connection. There are
only one format i SQL7 that is always interpreted in the same way and
that is YYYYMMDD.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp