December 28, 2010 at 9:48 am
Greetings again friends and helpers.
The code below grabs only month from getdate() and then day and year are static.
Given the code below, I get 12/2/2010 then minutes, seconds and am/pm
CONVERT(VARCHAR(2),GETDATE(),101) + '/2/2010 12:00:00 PM'
How can I replace the entire code so I get something like:
CONVERT(VARCHAR(2),GETDATE(),101) + then the day/year minutes:seconds AM or PM are db driven?
Is there a better way of rewriting the code above?
As usual, your assistance is greatly, greatly appreciated.
December 28, 2010 at 12:01 pm
I'm not sure what exactly you're looking for here. If you just want to replace the string with a column in a table, just do that.
If you want a specific date based on a column in a table, like you want to get the prior year back, or the prior month, or a week ago, then you'd use DateAdd instead of string functions.
Can you clarify what you have (some examples), and what you want to get from it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2010 at 12:25 pm
simflex-897410 (12/28/2010)
Greetings again friends and helpers.The code below grabs only month from getdate() and then day and year are static.
Given the code below, I get 12/2/2010 then minutes, seconds and am/pm
CONVERT(VARCHAR(2),GETDATE(),101) + '/2/2010 12:00:00 PM'How can I replace the entire code so I get something like:
CONVERT(VARCHAR(2),GETDATE(),101) + then the day/year minutes:seconds AM or PM are db driven?
Is there a better way of rewriting the code above?
As usual, your assistance is greatly, greatly appreciated.
Hi
I think your question is not clear,i assumed that you are trying to get some thing like this
Select CONVERT(VARCHAR,GETDATE(),101)+' '+CONVERT(VARCHAR,case when DATEPART(HH,getdate())>12 then 12-DATEPART(HH,getdate()) else DATEPART(HH,getdate()) end )+':'+CONVERT(VARCHAR,DATEPART(mi,getdate()))+':'+CONVERT(VARCHAR,DATEPART(SS,getdate()))
+' '+CONVERT(VARCHAR,case when DATEPART(HH,getdate())>12 then 'PM' else 'AM' end)
Thanks
Parthi
Thanks
Parthi
December 28, 2010 at 12:38 pm
Parthi:
That will generate negative hours if it's after noon. For example, 2 PM will end up as -2 PM. Not sure that's what you want.
Also, that kind of complex formatting doesn't belong in the database. It belongs in the presentation layer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2010 at 12:42 pm
Please use the datetime data type when working with datetime info, so you don't have to bother about formating and have all wonderful datetime related functions available.
Have a look at this article by Lynn Pettis "Some Common Date Routines"
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 28, 2010 at 1:14 pm
GSquared (12/28/2010)
Parthi:That will generate negative hours if it's after noon. For example, 2 PM will end up as -2 PM. Not sure that's what you want.
Also, that kind of complex formatting doesn't belong in the database. It belongs in the presentation layer.
hey it should be
Select CONVERT(VARCHAR,GETDATE(),101)+' '+CONVERT(VARCHAR,case when DATEPART(HH,getdate())>12 then DATEPART(HH,getdate()) -12 else DATEPART(HH,getdate()) end )+':'+CONVERT(VARCHAR,DATEPART(mi,getdate()))+':'+CONVERT(VARCHAR,DATEPART(SS,getdate()))
+' '+CONVERT(VARCHAR,case when DATEPART(HH,getdate())>12 then 'PM' else 'AM' end)
I just derived for the above.It is just to show that there are ways are there we derive thats it complication and performance things are the person to choose whether he can use or not that is his/her desire i'm just showing possible ways are there
Thanks
Parthi
Thanks
Parthi
December 29, 2010 at 8:38 am
What's wrong with:
/* day 2 of the current month 00:00:00.000 */
select dateadd(dd,1,dateadd(mm, datediff(mm, 0, getdate()), 0))
/* if you need day 2 12 noon */
select dateadd(hh,36,dateadd(mm, datediff(mm, 0, getdate()), 0))
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 29, 2010 at 9:27 am
ALZDBA (12/29/2010)
What's wrong with:
/* day 2 of the current month 00:00:00.000 */
select dateadd(dd,1,dateadd(mm, datediff(mm, 0, getdate()), 0))
/* if you need day 2 12 noon */
select dateadd(hh,36,dateadd(mm, datediff(mm, 0, getdate()), 0))
I don't think that's what he's looking for. I think he has a date and time in a table, and is looking for something from that. We may never know, since the OP hasn't responded to questions about it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply