June 30, 2010 at 12:54 pm
Hi
I need to get the date as in any formate but i should not use convert or cast to it i need in any formate say 20100630 or in 2010/06/30 any formate i desire
I tried this but it is also not coming i tried for 20100630 formate
select datepart(yy,getdate())+' '+datepart(m,getdate())+''+datepart(d,getdate())
any idea
Regards
Parthi
Thanks
Parthi
June 30, 2010 at 12:57 pm
K I have to ask why should you not use cast or convert?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 9:18 pm
Dan.Humphries (6/30/2010)
K I have to ask why should you not use cast or convert?
Just i am trying to find out any possiable ways are there other than this... you can keep this as learning a new thing
Thanks
Parthi
July 1, 2010 at 3:18 am
Select (select datepart(yy,getdate()) for XML Path(''))
+ '' +
(select datepart(mm,getdate()) for XML Path('') )
+ '' +
(Select datepart(dd,getdate()) for XML Path(''))
There might be another way as well...
July 1, 2010 at 5:08 am
parthi-1705 (6/30/2010)
Dan.Humphries (6/30/2010)
K I have to ask why should you not use cast or convert?Just i am trying to find out any possiable ways are there other than this... you can keep this as learning a new thing
There's a good reason for Dan asking this - CONVERT is almost certain to be the simplest and fastest way to change the data type (and hence format) of your date.
If you insist on writing slow overcomplex code, then the following might help. Note that the value returned by all of the functions is INT.
SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())
SELECT DATEPART(yy, GETDATE()), DATEPART(mm, GETDATE()), DATEPART(dd, GETDATE())
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2010 at 9:18 am
If you really want to be able to do anything with the data, I'd suggest performing that work on the client side, in code or the reporting tool or what have you. The tools for formatting are much greater there than they are in TSQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2010 at 12:08 pm
Chris Morris-439714 (7/1/2010)
parthi-1705 (6/30/2010)
Dan.Humphries (6/30/2010)
K I have to ask why should you not use cast or convert?Just i am trying to find out any possiable ways are there other than this... you can keep this as learning a new thing
There's a good reason for Dan asking this - CONVERT is almost certain to be the simplest and fastest way to change the data type (and hence format) of your date.
If you insist on writing slow overcomplex code, then the following might help. Note that the value returned by all of the functions is INT.
SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())
SELECT DATEPART(yy, GETDATE()), DATEPART(mm, GETDATE()), DATEPART(dd, GETDATE())
As i said in my previous post itself i am just trying to learn new things finding out what are the possiable ways are there other than normal process...
Atif has given out one way is there , like wise some other possiable ways will be there thats the thing i am finding out using of the query is secondary.
Just learning new things is not a bad one.
No one will guide on Complex queries
Thanks
Parthi
July 1, 2010 at 2:17 pm
Ok, I think maybe what you are getting at is "How can I choose a date format that is not available through CONVERT/CAST?"...
Here is a TVF that allows this.
(Written using lots of cross apply purely because I find this an easy way to code this kind of thing....)
alter function ufn_format_date (@date datetime, @format varchar(512))
returns table
as
-- Date formatting similar to .NET formatting but for T-SQL (2005+)
-- Possible format parameters are
-- d = 1 style day
-- dd = 01 style day
-- ddd = Mon style day
-- MM = 01 style month
-- MMM = Jan style month
-- yy/yyyy = 10/2010 style year
-- h = 1 style hour
-- hh = 01 style hour
-- m = 1 style minute
-- mm = 01 style minute
-- ss = 01 style seconds
-- n/nn/nnn = 1/11/111 style milliseconds
-- PP = AM/PM
return select FormattedDate
from (select 1) a(a)
cross apply (select replace(@format,'yyyy',convert(char(4),year(@date))) COLLATE Latin1_General_Bin) c1(f)
cross apply (select replace(c1.f,'yy',right(convert(char(4),year(@date)),2))) c2(f)
cross apply (select replace(c2.f,'nnn',right('000'+convert(varchar(3),datepart(millisecond,@date)),3))) c3(f)
cross apply (select replace(c3.f,'nn',left(right('000'+convert(varchar(3),datepart(millisecond,@date)),3),2))) c4(f)
cross apply (select replace(c4.f,'n',left(right('000'+convert(varchar(3),datepart(millisecond,@date)),3),1))) c5(f)
cross apply (select replace(c5.f,'ss',right('00'+convert(varchar(2),datepart(second,@date)),2))) c6(f)
cross apply (select replace(c6.f,'mm',right('00'+convert(varchar(2),datepart(minute,@date)),2))) c7(f)
cross apply (select replace(c7.f,'m',convert(varchar(2),datepart(minute,@date)))) c8(f)
cross apply (select replace(c8.f,'hh',right('00'+convert(varchar(2),datepart(hour,@date)),2))) c9(f)
cross apply (select replace(c9.f,'h',convert(varchar(2),datepart(hour,@date)))) c10(f)
cross apply (select replace(c10.f,'MMM',convert(varchar(3),datename(month,@date)))) c11(f)
cross apply (select replace(c11.f,'MM',right('00'+convert(varchar(2),datepart(month,@date)),2))) c12(f)
cross apply (select replace(c12.f,'ddd',convert(varchar(3),datename(weekday,@date)))) c13(f)
cross apply (select replace(c13.f,'dd',right('00'+convert(varchar(2),datepart(day,@date)),2))) c14(f)
cross apply (select replace(c14.f,'d',convert(varchar(2),datepart(day,@date)))) c15(f)
cross apply (select replace(c15.f,'HH',right('00'+convert(varchar(2),(case datepart(hour,convert(datetime,@date)) when 12 then 12 else datepart(hour,convert(datetime,@date))%12 end)),2))) c16(f)
cross apply (select replace(c16.f,'H',convert(varchar(2),(case datepart(hour,convert(datetime,@date)) when 12 then 12 else datepart(hour,convert(datetime,@date))%12 end)))) c17(f)
cross apply (select replace(c17.f,'PP',right(convert(varchar(32),@date,109),2))) c18(FormattedDate)
And some sample code to show how it works:
;with sample_data (SourceDate,Format) as
(
select getdate() ,'dd/MM/yy' union all
select getdate() ,'yyyyMMdd' union all
select getdate() ,'dd/MM/yyyy' union all
select getdate() ,'dd/MMM/yy' union all
select getdate() ,'dd/MMM/yyyy' union all
select getdate() ,'ddd MMM yyyy' union all
select getdate() ,'dd/MM/yy hh:mm' union all
select getdate() ,'dd/MM/yy HH:mm PP' union all
select getdate() ,'hh:mm:ss.nnn d MMM yyyy'
)
select SourceDate,Format,FormattedDate
from sample_data
cross apply dbo.ufn_format_date(SourceDate,Format)
And the output of that sample data run....
SourceDate Format FormattedDate
----------------------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-07-01 21:15:50.363 dd/MM/yy 01/07/10
2010-07-01 21:15:50.363 yyyyMMdd 20100701
2010-07-01 21:15:50.363 dd/MM/yyyy 01/07/2010
2010-07-01 21:15:50.363 dd/MMM/yy 01/Jul/10
2010-07-01 21:15:50.363 dd/MMM/yyyy 01/Jul/2010
2010-07-01 21:15:50.363 ddd MMM yyyy Thu Jul 2010
2010-07-01 21:15:50.363 dd/MM/yy hh:mm 01/07/10 21:15
2010-07-01 21:15:50.363 dd/MM/yy HH:mm PP 01/07/10 09:15 PM
2010-07-01 21:15:50.363 hh:mm:ss.nnn d MMM yyyy 21:15:50.363 1 Jul 2010
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply