how to get 03 from MONTH('03/12/1998')

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 2000 Forums

SQL Server Development (2000)

how to get 03 from MONTH('03/12/1998')

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-03-23 : 19:38:20

This example returns the number of the month from the date 03/12/1998.

SELECT "Month Number" = MONTH('03/12/1998')

Here is the result set:

Month Number
------------
3

IF I select year('03/12/1998'), MONTH('03/09/1998'), day('03/12/1998')

i get year 1998
mount 3 ( i need 03)
day 9 ( I need 09)

better yet i need 19980309 as a single number
how can i do that if always i use month i get the number without the left zeros?

tks
C. Lages

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-23 : 20:33:21

Try this on for size:

select replace(CONVERT ( varchar (10), getdate() , 120),'-','')

aught to do the trick...

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-23 : 23:55:04

Try like this:

SELECT "Month Number" = Right('0' + Convert(Varchar(2), MONTH('12/12/1998')), 2)

:) While we stop to think, we often miss our opportunity :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-24 : 00:00:28

select right('00'+convert(varchar(2),MONTH('03/12/1998')),2)
select right('00'+convert(varchar(2),day('03/12/1998')),2)

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 03:19:45

>> better yet i need 19980309 as a single number
select convert(numeric(8,0),convert(char(8),getdate(),112))

check the CONVERT function in BOL,
convert(char(8),getdate(),112) -- ISO date w century

rockmoose

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-24 : 09:47:53

Hey Rockmoose..
I missed the ISO including the century(That's what I get for reading an older copy of BOL)...
CLages,Rockmooses' and my solution give you the string you want in one function, whereas the others are doing it one field at a time (DD,MM,YYYY). All of them will work just fine...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-24 : 09:59:15

And, as always, I strongly recommend to do formatting at the presentation layer not within SQL. When you format something and return it to the client, you are no longer returning a datetime, you are returning a number or a varchar and forcing the data to be formatted in a specific manner which makes things less flexible and forces SQL Server to more work than it needs to.

- Jeff

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-24 : 10:06:54

How True!
We just got done going through every Crystal Report in the system (Holy Crapola where there a lot!), and one of the biggest problems from the original implementation was dates being reformatted in SQL into a string like this and then passed to Crystal. Far better to pass the datetime to the report and then convert it to whatever format you want; keeps things consistent on the server side.

BTW Jeff, we realized an 18% performance gain by removing all of the wierd formatting stuff from SQL and putting into the report side (only one table/formatting/report scenarion we couldn't do; see the posts on the denormalized

schedule stuff I've posted)!
That is a conciderable gain from just cleaning up report formatting..

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources