SQL Server Forums
Profile | Register | 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
11751 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
22431 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
22431 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
22772 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.09 seconds. Powered By: Snitz Forums 2000