| Author |
 Topic  |
|
|
-Dman100-
Posting Yak Master
208 Posts |
 Posted - 09/10/2004 : 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
India
277 Posts |
Posted - 09/11/2004 : 01:13:34
|
convert(char(10), @SmallDateTime, 101)
Hemanth Gorijala
BI Architect / DBA |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 09/11/2004 : 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
208 Posts |
Posted - 09/12/2004 : 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
208 Posts |
Posted - 09/12/2004 : 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
United Kingdom
12543 Posts |
Posted - 09/12/2004 : 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
USA
4184 Posts |
Posted - 09/12/2004 : 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 Posts |
Posted - 10/16/2004 : 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
United Kingdom
22191 Posts |
Posted - 10/16/2004 : 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
USA
4184 Posts |
Posted - 10/17/2004 : 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
United Kingdom
22191 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
| |
Topic |
|
date format
Forum Locked
