Format() Function in MS SQL

[sql guy123]

HI,

I'm pretty new to MS SQL,

My problem is setting ....

=Format$(Date(),"mm" & "/1/" & "YYYY")

(Which is from my MS ACCESS database)

as my default value in MS SQL. How can I do this since format() does
not seem to be a function that is in MS SQL. The above function works
great in MS ACCESS, but not in MS SQL.

thanks

[Erland Sommarskog]

There are *huge* differences between Access and SQL Server. First of all
SQL Server is a server-only program, while in Access, you have a mixup
of client and database in the same package. But there are also big
differences between Access and SQL Server in the SQL dialect.

For formating dates, you use the convert function:

SELECT convert(varchar, getdate(), nnn)

where nnn is a format code, each format code giving a different format. And
there is no room for customer format. Look up the topic CAST and CONVERT in
Books Online to see the available format codes.

Best practice, though, is to return dates as is from SQL Server, and do
the formatting in the client, so the user's regional settings can be
taken into account.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[sql guy123]

I'm actually not really trying to format the date, but rather set the
default value of the column(field) to the first day of the month.

example: 1/1/2005, 2/1/2005, 3/1/2005 etc...

What is the best way to get the default value in MS SQL to be the first
day of the month?

[Stu]

try
=CONVERT(smalldatetime,DATENAME(m, GETDATE()) + ' 1,' + DATENAME(yy,
GETDATE()))

HTH,
Stu

[Erland Sommarskog]

convert(datetime, convert(char(6), getdate(), 112) + '01')