default date format

Question

default date format

lotusnotes

SSC Eights!

Points: 833
More actions
November 17, 2003 at 7:25 am

#59551

this has got to be annoyingly simple but how do you change the default date format?
select getdate()
returns 2003-11-17 14:05:33.013.
How do I get 17-11-2003?
Thanks

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Use the CONVERT function, e.g.:



SELECT CONVERT(char(10),GETDATE(),105)

--Jonathan

lotusnotes SSC Eights! Points: 833 More actions · Answer 2

but is there a way of changing the underlying default so I don't have to run a convert?

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
but is there a way of changing the underlying default so I don't have to run a convert?

You're just seeing the "underlying default" of whatever front-end you use, so format the value with your front-end. Dates are dates; what you see is the front-end's presentation. For example, with Query Analyzer (QA), change your workstation's regional settings to, say, Italian (or just customize the Short date format to "dd/mm/yyyy"), and then on QA's Tools|Options|Connections screen, check the Use regional settings when displaying currency, number, dates, and times option.

--Jonathan

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 4

i used this one to convert :

CAST(fld AS smalldatetime)

Regards

J

JV

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 5

i used this one to convert :

CAST(fld AS smalldatetime)

Regards

J

JV

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 6

i used this one to convert :

CAST(fld AS smalldatetime)

Regards

J

JV

gsolomon Mr or Mrs. 500 Points: 599 More actions · Answer 7

gsolomon

Mr or Mrs. 500

Points: 599

November 18, 2003 at 6:58 am

#482419

JV which one do you use again?

richard102 Grasshopper Points: 23 More actions · Answer 8

type 'convert' in query analyser, highlight it, an press shift and f1. To find help on the convert command, including a large table of the data-formats.

sainswor99 SSC Veteran Points: 230 More actions · Answer 9

JV,

The nice thing about CONVERT vs CAST is that CONVERT supports a wide range of style codes as opposed to CAST.

See the BOL for a fuller explanation, but ultimately, it matters not when doing actual data comparison.

Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?

Stu

Edited by - sainswor99 on 12/05/2003 1:01:53 PM

mgara SSC Rookie Points: 29 More actions · Answer 10

I think it is the easiest way

select * from Track

where

Track.Track_Open_Date = CAST(CONVERT(char(10),GETDATE(),102) AS smalldatetime))

philcart SSC-Forever Points: 47794 More actions · Answer 11

quote:
Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?

Ok I'm not exactly a beginning DBA, but I'm always up for a brainteaser. Does the result need to be in datetime format, or a specific date format eg: dd-mm-yy?

If not then you can do

CAST(<date value> as varchar(11))

this will return a varchar date in the format of mmm dd yyyy.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

sainswor99 SSC Veteran Points: 230 More actions · Answer 12

quote:
quote:
Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?
Ok I'm not exactly a beginning DBA, but I'm always up for a brainteaser. Does the result need to be in datetime format, or a specific date format eg: dd-mm-yy?
If not then you can do
CAST(<date value> as varchar(11))
this will return a varchar date in the format of mmm dd yyyy.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Close enough 🙂

I typically use CONVERT(varchar(11), @Date, [style]) instead of CAST, only because it gives me the flexibility of changing the format a bit. CAST will work just fine, however 🙂

Stu

Edited by - sainswor99 on 11/27/2003 6:41:59 PM

philcart SSC-Forever Points: 47794 More actions · Answer 13

I agree CONVERT does have the advantage of being able to change the output format. It's also handy to bear in mind that you can use CONVERT's type parameter for converting float, real, money and smallmoney.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Jonathan SSC-Insane Points: 20427 More actions · Answer 14

quote:
Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?
Stu

Quickest performing?



SUBSTRING(CAST(<datetime value> AS binary(8)),1,4)

Quickest to type?



LEFT(<datetime value>,11)

--Jonathan (beginning DBA )

--Jonathan