date format

Question

date format

BeginnerBug

Hall of Fame

Points: 3539
More actions
December 14, 2010 at 6:52 am

#144021

TO_DATE('11/17/2005 20:51:27', 'MM/DD/YYYY HH24:MI:SS')
above function in Oracle.. wht is the equavalent in sql server for the format of date 'MM/DD/YYYY HH24:MI:SS' ?
any help pls?

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

This has all the data on formatting dates in T-SQL: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Honestly, though, you should be formatting dates in the presentation layer, not in the database or data access layer. Doing it at presentation allows for localization, personal options, et al, doing it in lower layers blocks those options.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I think the CONVERT function is what you're looking for. Or you could allow the data layer to store the data and leave the formatting to the presentation layer.

John

GSquared SSC Guru Points: 260824 More actions · Answer 3

John Mitchell-245523 (12/14/2010)
I think the CONVERT function is what you're looking for. Or you could allow the data layer to store the data and leave the formatting to the presentation layer.
John

Jinx!

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

MonsterRocks (12/14/2010)
TO_DATE('11/17/2005 20:51:27', 'MM/DD/YYYY HH24:MI:SS')
above function in Oracle.. wht is the equavalent in sql server for the format of date 'MM/DD/YYYY HH24:MI:SS' ?
any help pls?

I know this post is several days old but why is it necessary to format a date in the database instead of more properly in the GUI?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 5

s jeff.. i realized that.. i made it in presentation layer

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

Perfect. Thanks for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 7

Hi FIOlks

can someone tell me what I am not doing correctly

the first sql works but the second does not

vercdate is a datetime field

can someone explain to me why the first works and the second does not:

what am i missing here

select

COUNT (docid) as 'Version Count'

from

dversdata

where

vercdate >= '20090101 00:00:00'

and

vercdate <= '20091231 23:59:59';

go

Version Count

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

2489931

select

COUNT (docid) as 'Version Count'

from

dversdata

where

WHERE vercdate BETWEEN '20090101 00:00:00' AND '20091231 23:59:59';

go

Msg 156, Level 15, State 1, Server NSAB-SS73-SQL-N, Line 6

Incorrect syntax near the keyword 'WHERE'.

Thanks

Jim

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Michael Valentine Jones

SSC Guru

Points: 64818

February 28, 2011 at 10:22 am

#1292432

You have "where" twice.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 9

Damn !

thanks Michael

it's Monday 🙂

I must have stared at that for sometime and it did not even compute

Jim