help trimming date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help trimming date

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-12 : 09:25:32

Hi,

How can I trim a datetime column value as such

2009-01-11 00:00:00.000

to

"01-11" ?

any help much appreciated!

thanks once again! :)
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 09:27:27

[code]SELECT DATENAME(mm,datecol)+'-'+DATENAME(dd,datecol) FROM Table[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-12 : 09:38:40

Hey Visakh,

I've been trying on this, but havent had any luck. I think I am doing it incorrectly

SELECT dateadd(day, datediff(day, 0, clickDate), 0) as clickDate

How would I integrate into this one?

Thanks again, much appreciated! :)
mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-12 : 09:42:06

quote:
Originally posted by mike123

Hi,

How can I trim a datetime column value as such

2009-01-11 00:00:00.000

to

"01-11" ?

any help much appreciated!

thanks once again! :)
mike123

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-12 : 09:45:28

Madhivanan,

Not sure I understnad what your asking. This data is being brought back to a web app?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-12 : 09:49:11

quote:
Originally posted by mike123

Madhivanan,

Not sure I understnad what your asking. This data is being brought back to a web app?

Then return date as such and do the formation at the web page

Madhivanan

Failing to plan is Planning to fail

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-12 : 09:52:07

Ah ok, that is definately an option if this is too ugly to do in SQL, is that what your saying?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 10:06:09

quote:
Originally posted by mike123

Ah ok, that is definately an option if this is too ugly to do in SQL, is that what your saying?

it is . because formatting is not purpose of sql. it needs to be done at front end if at all possible.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-13 : 07:24:08

i think its gonna work,,

Substring(date,5,5)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 07:36:01

quote:
Originally posted by visakh16

SELECT DATENAME(mm,datecol)+'-'+DATENAME(dd,datecol) FROM Table

How about leading zero, visakh?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 07:38:25

See Books Online for convert function. You most certainly need style 110 in this case.

SELECT CONVERT(CHAR(5), clickDate, 110) -- Only 5 first characters
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-13 : 07:38:47

check this link for date formats
www.sql-server-helper.com/tips/date-formats.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 07:54:37

Why not this link?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-01-13 : 08:19:19

I tend to use the option that Peso is using when I need to format the date for outputs etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 08:22:15

If I remember this correctly, Mike123 is going to use the formatted datevalue in an XML output, hence cannot format at front end.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 08:40:56

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

SELECT DATENAME(mm,datecol)+'-'+DATENAME(dd,datecol) FROM Table

How about leading zero, visakh?

E 12°55'05.63"
N 56°04'39.26"

SELECT RIGHT('0'+ DATENAME(mm,datecol),2)+'-'+DATENAME(dd,datecol) FROM Table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 09:22:50

Ah! I see... Much easier than

SELECT CONVERT(CHAR(5), clickDate, 110)

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-01-13 : 11:50:09

hey guys, thanks! problem solved :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts