By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,899 Members | 1,353 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,899 IT Pros & Developers. It's quick & easy.

Format() Function in MS SQL

P: n/a
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

Nov 28 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
sql guy123 (st*****@hotmail.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 28 '05 #2

P: n/a
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?

Nov 29 '05 #3

P: n/a
Stu
try
=CONVERT(smalldatetime,DATENAME(m, GETDATE()) + ' 1,' + DATENAME(yy,
GETDATE()))

HTH,
Stu

Nov 29 '05 #4

P: n/a
sql guy123 (st*****@hotmail.com) writes:
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?


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

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 29 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.