November 7, 2011 at 8:01 am
I need to format datetime as below:
01/12/2011 09:28:22 AM
How to code it?
November 7, 2011 at 8:21 am
You don't need to format dates.
Let the application do it.
-- Gianluca Sartori
November 7, 2011 at 8:25 am
I need to pass getdate() to get current date and time string and then pass it to another function.
November 7, 2011 at 8:42 am
adonetok (11/7/2011)
I need to format datetime as below:01/12/2011 09:28:22 AM
How to code it?
You do not need to format a datetime in SQL, it's a presentation layer task.
If you insist, it can be done like this: -
DECLARE @theDate DATETIME
SET @theDate = GETDATE()
SELECT GETDATE() AS standardDateTime,
CONVERT(VARCHAR(20),@theDate,3) + ' ' + RIGHT('0'+LTRIM(SUBSTRING(CONVERT(VARCHAR(20),@theDate,0),12,20)),7) AS [ugly dd/mm/yyyy],
CONVERT(VARCHAR(20),@theDate,1) + ' ' + RIGHT('0'+LTRIM(SUBSTRING(CONVERT(VARCHAR(20),@theDate,0),12,20)),7) AS [ugly mm/dd/yyyy]
If you do that in the presentation layer, it's much simpler - something like this: -
Format(DateTime.Now,dt)
adonetok (11/7/2011)
I need to pass getdate() to get current date and time string and then pass it to another function.
Then your other function is badly coded and needs looking at. If it's T-SQL, recode it to accept a DATETIME instead.
November 7, 2011 at 8:51 am
Why don't you use datetime type?
Regards,
Iulian
November 7, 2011 at 8:57 am
I need pass exact string format from getdate() to third party application in which I can not do anything.
The third party applicaton only take format like below(including space):
'11/07/2011 09:30:00 AM'
'12/11/2011 03:15:00 PM'
November 7, 2011 at 9:23 am
CONVERT(char(10),GETDATE(),101)+' '+STUFF(REPLACE(STUFF(CONVERT(char(26),GETDATE(),109),1,12,''),' ','0'),9,4,' ')
Far away is close at hand in the images of elsewhere.
Anon.
November 7, 2011 at 9:25 am
adonetok (11/7/2011)
I need pass exact string format from getdate() to third party application in which I can not do anything.The third party applicaton only take format like below(including space):
'11/07/2011 09:30:00 AM'
'12/11/2011 03:15:00 PM'
As the others have mentioned, formatting dates is not what SQL Server does best.
Is the function in the third-party application a T-SQL Function or in a web service or other application?
If it is T-SQL how is the application stopping you from passing a date in any other format?
If it is not a T-SQL function then you must be using a middle layer between your T-SQL and the function, so you should do the formatting in that middle layer.
Now to answer your question, since I've gotten my opinions, questions, and caveats out of the way. You need to use the CONVERT function with the style parameter. See BOL here. The format you need may actually require you to do 2 Converts and a concatenation so you can get the date in the right format and the time in thee right format. If you read the BOL link you should be able to figure it out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 7, 2011 at 9:38 am
adonetok (11/7/2011)
I need pass exact string format from getdate() to third party application in which I can not do anything.The third party applicaton only take format like below(including space):
'11/07/2011 09:30:00 AM'
'12/11/2011 03:15:00 PM'
Still can't tell whether you're after dd/mm/yyyy or mm/dd/yyyy.
DECLARE @theDate DATETIME
SET @theDate = GETDATE()
SELECT @theDate,
CONVERT(VARCHAR(20),@theDate,3) + ' ' + RIGHT('0'+ LTRIM(SUBSTRING(REPLACE(REPLACE(CONVERT(VARCHAR(26),@theDate,9),CONVERT(VARCHAR(2),N)+'AM',' AM'),CONVERT(VARCHAR(2),N)+'PM',' PM'),12,26)),22) AS [ugly dd/mm/yyyy],
CONVERT(VARCHAR(20),@theDate,1) + ' ' + RIGHT('0'+ LTRIM(SUBSTRING(REPLACE(REPLACE(CONVERT(VARCHAR(26),@theDate,9),CONVERT(VARCHAR(2),N)+'AM',' AM'),CONVERT(VARCHAR(2),N)+'PM',' PM'),12,26)),22) AS [ugly mm/dd/yyyy]
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(N)
WHERE CHARINDEX(' ',LTRIM(SUBSTRING(REPLACE(REPLACE(CONVERT(VARCHAR(26),@theDate,9),CONVERT(VARCHAR(2),N)+'AM',' AM'),CONVERT(VARCHAR(2),N)+'PM',' PM'),12,26))) > 1
Returns: -
ugly dd/mm/yyyy ugly mm/dd/yyyy
----------------------- ------------------------------------------- -------------------------------------------
2011-11-07 16:38:10.657 07/11/11 04:38:10:65 PM 11/07/11 04:38:10:65 PM
Arrrgh. Misread your expected output.
Code should have been like this: -
DECLARE @theDate DATETIME
SET @theDate = GETDATE()
SELECT @theDate,
CONVERT(VARCHAR(20),@theDate,3) + ' ' + RIGHT('0'+ LTRIM(SUBSTRING(REPLACE(REPLACE(':'+CONVERT(VARCHAR(26),@theDate,9),CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'AM',' AM'),':'+CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'PM',' PM'),13,26)),22) AS [ugly dd/mm/yyyy],
CONVERT(VARCHAR(20),@theDate,1) + ' ' + RIGHT('0'+ LTRIM(SUBSTRING(REPLACE(REPLACE(':'+CONVERT(VARCHAR(26),@theDate,9),CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'AM',' AM'),':'+CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'PM',' PM'),13,26)),22) AS [ugly mm/dd/yyyy]
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(N), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) b(N),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) c(N)
WHERE CHARINDEX(' ',LTRIM(SUBSTRING(REPLACE(REPLACE(':'+CONVERT(VARCHAR(26),@theDate,9),CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'AM',' AM'),':'+CONVERT(VARCHAR(1),a.N)+CONVERT(VARCHAR(1),b.N)+CONVERT(VARCHAR(1),c.N)+'PM',' PM'),13,26))) > 1
Which returns: -
ugly dd/mm/yyyy ugly mm/dd/yyyy
----------------------- ------------------------------------------- -------------------------------------------
2011-11-07 16:47:30.573 07/11/11 04:47:30 PM 11/07/11 04:47:30 PM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply