DATEFORMAT

Question

Post reply

DATEFORMAT

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

This will successfully convert characters in the DD/MM/YYYY format into a DateTime datatype without much fanfare...

CONVERT(DATETIME, yourstringdate, 103)

Here's an example you can try...

DECLARE @ddmmyyyyString CHAR(10)

SET @ddmmyyyyString = '25/01/2006'

DECLARE @MyDateTime DATETIME

SET @MyDateTime = CONVERT(DATETIME,@ddmmyyyyString,103)

SELECT @MyDateTime

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

I just gotta remember to read the last page ... Serqiy already made a similar suggestion. Guess a concurrence never hurts though...

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

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 3

If you want to guarantee that Regional Settings (of the user, the workstation, or the server) do not cause havoc with your dates, I highly recommend passing dates to the SP in the format:

dd-mmm-yyyy

For example

03-FEB-2006

MSSQL will always correctly convert this string to DATETIME, irrespective of Regional Settings. There is no chance of this date being interpreted as 2nd March, for example.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

FYI

English is not only language on Earth.

_____________
Code for TallyGenerator

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 5

hmm, good point. However, the main problem here is the variation between US date format, rest-of-world date format (ok, not quite true) and ISO date format.

I would like to recommend ISO standard, but in my experience I have had odd results (mainly with Microsoft Access database, it must be said), but sufficient to put me off using it.

I would also suggest that none of the solutions here would work with the Chinese calendar (which is far more complex than just saying it is now the year of the dog).

vadba SSChampion Points: 11133 More actions · Answer 6

If you use the yyyymmdd format, it will always work regardless of the regional settings or SET DATEFORMAT state. Plus, no U.S. month names, just numbers. Since today is Feb 3, 2006, you would use '20060203'

dhananjaneyulud Grasshopper Points: 19 More actions · Answer 7

instead of going concatinating like this

@EndDate + '23:59:00.000'

you can use convert function and then convert to sepcified format

say

CONVERT(DATETIME,CONVERT(VARCHAR,getdate(),101),101)

gives the following format.

2006-02-06 00:00:00.000

hope this will helpfull.

Dhanu 🙂