date format - SQL Server Forums

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

All Forums

Old Forums

CLOSED - General SQL Server

date format

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-09-10 : 21:24:12

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

277 Posts

Posted - 2004-09-11 : 01:13:34

convert(char(10), @SmallDateTime, 101)

Hemanth Gorijala
BI Architect / DBA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-11 : 14:42:43

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

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-09-12 : 17:32:33

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-

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-09-12 : 17:45:45

On second look, could I change the default datetime data type to another format using a rule? Not a user-defined data type. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-12 : 19:13:28

>> 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.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-12 : 22:24:47

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.

anazri
Starting Member

1 Post

Posted - 2004-10-16 : 05:32:51

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

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 06:11:40

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-17 : 13:19:36

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.

Kristen
Test

22859 Posts

Posted - 2007-01-01 : 04:42:36

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

(For anyone reaching this post from the FAQ

)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 06:28:54