Date String

Question

Date String

Scott Holmes-446462

Mr or Mrs. 500

Points: 556
More actions
March 25, 2008 at 4:57 am

#185473

Morning All
Id like to format a Date String to look like the following using T-SQL:
'Tue, 01 Jan 2002'
Could anyone give me a hand with the code im stuck?
Thanks, Scott

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Scott Holmes (3/25/2008)
Morning All
Id like to format a Date String to look like the following using T-SQL:
'Tue, 01 Jan 2002'
Could anyone give me a hand with the code im stuck?
Thanks, Scott

Hi Scott.

Try this:

SELECT

CASE DATEPART(weekday,GETDATE())

WHEN 1 THEN 'Sun'

WHEN 2 THEN 'Mon'

WHEN 3 THEN 'Tue'

WHEN 4 THEN 'Wed'

WHEN 5 THEN 'Thur'

WHEN 6 THEN 'Fri'

WHEN 7 THEN 'Sat'

END + ', ' + CONVERT(VARCHAR,GETDATE(),106)

Note that, by default, Sunday is the first day of the week but you can change this using the SET DATEFIRST option.

Hope that helps,

Karl
source control for SQL Server

Scott Holmes-446462 Mr or Mrs. 500 Points: 556 More actions · Answer 2

Scott Holmes-446462

Mr or Mrs. 500

Points: 556

March 25, 2008 at 5:23 am

#793383

Excellent, works perfectly

Thank you very much.

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 3

You don't need to worry about DateFirst with the following...

[font="Courier New"] SELECT LEFT(DATENAME(dw,GETDATE()),3)

+ ', ' + CONVERT(VARCHAR,GETDATE(),106)[/font]

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

Scott Holmes-446462 Mr or Mrs. 500 Points: 556 More actions · Answer 4

Thanks Jeff, just got back after being on holiday for 2 weeks. I will try to remember what i was doing and see if your version works better for me

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 5

So... did it?

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

Scott Holmes-446462 Mr or Mrs. 500 Points: 556 More actions · Answer 6

Man, if i had a memory id be dangerous!

It did yes thanks, used it in a few other places as well!

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 7

Heh... no problem... I didn't remember either... was reviewing old posts that I had flagged 'cause I knew I'd forget.

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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Flag a post? how?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 9

Sorry... didn't mean to get you excited.

"Flag" a post, in this case, meant that I save the URL for the post in my "Things to follow up on" list that I keep in a text file. There's not way to actually flag a post that I know of.

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

Scott Holmes-446462 Mr or Mrs. 500 Points: 556 More actions · Answer 10

You can subscribe to any Topic, and soon as a reply hits you get an email. Think thats the best you can do

Scott Holmes-446462 Mr or Mrs. 500 Points: 556 More actions · Answer 11

Jeff Moden (5/22/2008)
Sorry... didn't mean to get you excited.
"Flag" a post, in this case, meant that I save the URL for the post in my "Things to follow up on" list that I keep in a text file. There's not way to actually flag a post that I know of.

Nearly missed this, you save it in a text file and NOT an Access Database, with forms, reports and reminders being sent....

The shame

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 12

Heh... nope... none of that... I don't even join it with a Tally Table to preserve the dates or anything... it just bubbled to the top on a simple text file being used as a "stack".

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jeff Moden (5/22/2008)
Sorry... didn't mean to get you excited.
"Flag" a post, in this case, meant that I save the URL for the post in my "Things to follow up on" list that I keep in a text file. There's not way to actually flag a post that I know of.

#$$%^&^&***(. Oh well... I was kind of hoping I had missed something obvious all this time.

I tend to access SSC from a few different machines, so unless I plan on carrying around that text file on a memory stick, ain't going to help much.

I suppose I should be more stringent about using the "subscribed topic" just for these...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Date String

Cookies on SQLServerCentral