Date format no matter the server settings

Question

Date format no matter the server settings

Digs

SSCertifiable

Points: 7333
More actions
March 17, 2010 at 10:56 pm

#230975

Ok..
My local host sql server date format is : MM/dd/yyyy
But my hosting sql server date form is : dd/MM/yyyy
I have varchar fields within a table dates like MM/dd/yyyy ie 03/13/2010
So I need to convert the varchar text/string date of 03/13/2010 into a date dd/MM/yyyy (with out the 00:00:00) but still remain a string no matter the hosting server datetime settings are ??? So code that re aligns to hosting server of the day settings.
So result would be 13/03/2010 ( as varchar) ??
Any ideas

Viewing 8 posts - 1 through 8 (of 8 total)

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

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

Digs (3/17/2010)
Any ideas

Yes... NONE of these problems would exist if the dates and times were properly stored as DATETIME data instead of VARCHAR data.

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

My first question is: Why are you using date as string/varchar?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Digs SSCertifiable Points: 7333 More actions · Answer 3

Wow.. I better retreat.:w00t:

Ok so I think I will change the field to DATETIME.

So how can i get this DATETIME field into a VARCHAR that looks like MM/dd/yyy

With out the 00:00:00

Thanks

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 4

youre first post

So I need to convert the varchar text/string date of 03/13/2010 into a date dd/MM/yyyy (with out the 00:00:00)

youre second post

So how can i get this DATETIME field into a VARCHAR that looks like MM/dd/yyy
With out the 00:00:00

Why not use datetime on both ends, it will eliminate alot of headaches for you, and less maintenance and conversions etc.

Ok, lets do it your way...

Lets say you have a datetime column, and convert it to varchar...this is what you'll end up with...

declare @string varchar(20)

set @string = GETDATE()

select RTRIM(CONVERT(varchar(11), @string)) as Result

Result: 'Mar 18 2010'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Paul White SSC Guru Points: 150468 More actions · Answer 5

Digs (3/17/2010)
Ok so I think I will change the field to DATETIME.
So how can i get this DATETIME field into a VARCHAR that looks like MM/dd/yyy

If, for some reason, you absolutely have no choice but to return the DATETIME value as a string for output, you can the CONVERT function, specifying a particular style.

I am assuming you really want mm/dd/yyyy (there is no three-year-digit format):

PRINT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101);

Notice also that mm/dd/yyyy is a fixed-length format, so I used CHAR(10), not VARCHAR.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Digs SSCertifiable Points: 7333 More actions · Answer 6

Digs

SSCertifiable

Points: 7333

March 18, 2010 at 2:22 am

#1135782

Thanks

Paul White SSC Guru Points: 150468 More actions · Answer 7

Digs (3/18/2010)
Thanks

😎

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi