date formatting question

Question

Post reply

date formatting question

Mh-397891

SSCertifiable

Points: 6704
More actions
November 30, 2008 at 5:53 pm

#194515

I have been doing the following for the date format:
convert(varchar,DateColumn,101) + 00:00:00
How can I do the above without concatenating 00:00:00, I do'nt think concatenating is the best way of doing it.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

If your intent is to strip the time (or zero the time), then try:

SELECT dateadd(day, datediff(day, 0, DateColumn), 0);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Of course, that still doesn't produce a full string formatted version. Which may or may not have been the OP's intent, it's hard to say....

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Unless the output is directed at a format-defined file, date formatting should be done 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)

shailesh-519005 SSChasing Mays Points: 643 More actions · Answer 4

Hi Mh,

Try this....

cast(convert(varchar,cast(datecolumn as smalldatetime),101) as smalldatetime)

Thanks

Shailesh

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

shailesh (11/30/2008)
Hi Mh,
Try this....
cast(convert(varchar,cast(datecolumn as smalldatetime),101) as smalldatetime)
Thanks
Shailesh

Have you read previous replies? 😉

Madhivanan

Failing to plan is Planning to fail