Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 date format
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

-Dman100-
Posting Yak Master

210 Posts

Posted - 09/10/2004 :  21:24:12  Show Profile
How can I format the date into the format: mm/dd/yyyy

I'm using the smalldatetime, which formats: mm/dd/yyyy Hour:Min:Sec

I need to convert the format into the basic mm/dd/yyyy

How can I do this? Thanks.
-D-

hgorijal
Constraint Violating Yak Guru

India
277 Posts

Posted - 09/11/2004 :  01:13:34  Show Profile
convert(char(10), @SmallDateTime, 101)

Hemanth Gorijala
BI Architect / DBA
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 09/11/2004 :  14:42:43  Show Profile  Visit spirit1's Homepage
but formating should really be done in presentation layer.
because datetime has both parts mm/dd/yy and hh:mm:ss so you can't save it with just mm/dd/yy.
if no time is given date will be saved with 00:00:00.


Go with the flow & have fun! Else fight the flow
Go to Top of Page

-Dman100-
Posting Yak Master

210 Posts

Posted - 09/12/2004 :  17:32:33  Show Profile
How about using a user defined data type? When using the datetime data type the date format is:
mm/dd/yyyy hour:minute:second
9/12/2004 4:04:30PM

I want the format for the field to be:
mm/dd/yyyy

The same as the date/time format within Access. I've tried using convert...i.e. convert(char(10), field_name, 101) AS field_name

Can this be done using a user defined data type within EM? If so, how?
Thanks.
-D-
Go to Top of Page

-Dman100-
Posting Yak Master

210 Posts

Posted - 09/12/2004 :  17:45:45  Show Profile
On second look, could I change the default datetime data type to another format using a rule? Not a user-defined data type. Thanks.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/12/2004 :  19:13:28  Show Profile  Visit nr's Homepage
>> When using the datetime data type the date format is:
mm/dd/yyyy hour:minute:second

No it's not - it's a decimal number.
That's just the default way your client converts to chanracter for display.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 09/12/2004 :  22:24:47  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Basically, you can't and shouldn't try to change the default datetime datatype. You store it as a datetime, including the time. When you want to look at it, just use one of the convert functions. I agree it's "usually" a display issue; however, many times it's also a functionality issue and you should use the tools SQL provides to allow for that functionality. Having said that, once again here are the available datatype-to-char conversions:


SET NOCOUNT ON

DECLARE
	@min INT,
	@max INT,
	@date DATETIME

SELECT
	@min = 1,
	@max = 131,
	@date = GETDATE()

SELECT @date

WHILE @min <= @max
BEGIN

	IF @min BETWEEN 15 AND 19 
		OR @min  = 26 
		OR @min BETWEEN 27 AND 99
		OR @min BETWEEN 115 AND 119
		OR @min BETWEEN 122 AND 125
		OR @min BETWEEN 127 AND 129
		BEGIN
			GOTO NEXT_LOOP
		END

	SELECT @min, CONVERT(VARCHAR,@date,@min)

NEXT_LOOP:

SELECT @min = @min + 1
END



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

anazri
Starting Member

1 Posts

Posted - 10/16/2004 :  05:32:51  Show Profile
quote:
Originally posted by -Dman100-

How about using a user defined data type? When using the datetime data type the date format is:
mm/dd/yyyy hour:minute:second
9/12/2004 4:04:30PM

I want the format for the field to be:
mm/dd/yyyy

The same as the date/time format within Access. I've tried using convert...i.e. convert(char(10), field_name, 101) AS field_name

Can this be done using a user defined data type within EM? If so, how?
Thanks.
-D-




hi all, im a newbie

i'd like to ask about datetime conversion to time HH:MM:SS.

as example above we have datetime to date conversion.
do we use the same command " convert(char(10), field_name, 101) AS field_name " and just change the number 101 to any figure ..?

thank you
Go to Top of Page

Kristen
Test

United Kingdom
22858 Posts

Posted - 10/16/2004 :  06:11:40  Show Profile
Well ... the 101 is not just any number I'm afraid. They are a few prescribed formats (see Books on Line for details). 108 will give you hh:mm:ss

SELECT CONVERT(varchar(8), GetDate(), 108)

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 10/17/2004 :  13:19:36  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
You are right though. That's how the date converstions work. The figures you can change 101 to are in that script I posted earlier. It has to be one of those combinations. Run it to see what I'm talking about.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22858 Posts

Posted - 01/01/2007 :  04:42:36  Show Profile
See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862

(For anyone reaching this post from the FAQ )

Edited by - Kristen on 01/01/2007 06:54:13
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 01/01/2007 :  06:28:54  Show Profile  Send madhivanan a Yahoo! Message
quote:
Originally posted by Kristen

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862


Well timed post Kristen

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000