July 11, 2012 at 11:57 am
How do you make this:
yyyy/mm/dd hh:mm:ss
Look like this:
mm/dd/yyyy hh:mm --hh:mm needs to be in military time
July 11, 2012 at 12:00 pm
This has pretty much every format imaginable for datetimes. http://msdn.microsoft.com/en-us/library/ms187928.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2012 at 12:02 pm
Are these date/time values stored as character strings or as date/time values in a columns defined datetime or datetime2?
July 11, 2012 at 12:18 pm
sqluser_8119 (7/11/2012)
How do you make this:yyyy/mm/dd hh:mm:ss
Look like this:
mm/dd/yyyy hh:mm --hh:mm needs to be in military time
DECLARE @date datetime = '2012/06/20'
SELECT CONVERT(CHAR(10), CONVERT(datetime, @date,103),101)
-- Itzik Ben-Gan 2001
July 11, 2012 at 10:58 pm
PLS TRY BELOW CODE
SELECT DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0)
DECLARE @date datetime = '2012/06/20 11:05:02 PM'
DECLARE @HH INT=SUBSTRING(CONVERT(VARCHAR(10),@DATE,108),1,CHARINDEX(':',CONVERT(VARCHAR(10),@DATE,108))-1)
SELECT CONVERT(CHAR(10), CONVERT(datetime, @date,103),101)+' '+CONVERT(VARCHAR(10),CASE WHEN @date LIKE '% PM' THEN
CASE WHEN @HH=1 THEN 13
WHEN @HH=2 THEN 14
WHEN @HH=3 THEN 15
WHEN @HH=4 THEN 16
WHEN @HH=5 THEN 17
WHEN @HH=6 THEN 18
WHEN @HH=7 THEN 19
WHEN @HH=8 THEN 20
WHEN @HH=9 THEN 21
WHEN @HH=10 THEN 22
WHEN @HH=11 THEN 23
WHEN @HH=12 THEN 24 END ELSE @HH END)
+':'
+SUBSTRING(CONVERT(VARCHAR(10),@DATE,108),CHARINDEX(':',CONVERT(VARCHAR(10),@DATE,108))+1,LEN(CONVERT(VARCHAR(10),@DATE,108)))
July 12, 2012 at 7:09 am
subbareddy542 (7/11/2012)
PLS TRY BELOW CODESELECT DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0)
DECLARE @date datetime = '2012/06/20 11:05:02 PM'
DECLARE @HH INT=SUBSTRING(CONVERT(VARCHAR(10),@DATE,108),1,CHARINDEX(':',CONVERT(VARCHAR(10),@DATE,108))-1)
SELECT CONVERT(CHAR(10), CONVERT(datetime, @date,103),101)+' '+CONVERT(VARCHAR(10),CASE WHEN @date LIKE '% PM' THEN
CASE WHEN @HH=1 THEN 13
WHEN @HH=2 THEN 14
WHEN @HH=3 THEN 15
WHEN @HH=4 THEN 16
WHEN @HH=5 THEN 17
WHEN @HH=6 THEN 18
WHEN @HH=7 THEN 19
WHEN @HH=8 THEN 20
WHEN @HH=9 THEN 21
WHEN @HH=10 THEN 22
WHEN @HH=11 THEN 23
WHEN @HH=12 THEN 24 END ELSE @HH END)
+':'
+SUBSTRING(CONVERT(VARCHAR(10),@DATE,108),CHARINDEX(':',CONVERT(VARCHAR(10),@DATE,108))+1,LEN(CONVERT(VARCHAR(10),@DATE,108)))
I would be seriously concerned about performance if this is used. This is living proof that formatting should not be handled by sql server. Push formatting to the front end whenever possible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2012 at 7:45 am
CELKO (7/11/2012)
There is only one display format in ANSI SQL; the ISO-8601 version with dashes "yyyy-mm-dd HH:MM:SS.sssssss". Please notice I said display format.SQL is an abstract model of data. We do not care how it is kept internally because in an tiered architecture will pass it to a presentation layer that will turn the internal format into a local display of some kind. This is how client/server works.
Your mindset is still back in monolithic code where there are no tiers. You are still programming in 1950's COBOL where the data was kept in strings and your programs worked with it that way.
The CONVERT string function is a left-over from the early days of Sybase when we actually tried to write monolithic T-SQL. You need to catch up with the last 35 years of programming
Um, the question was:
How do you make this:
yyyy/mm/dd hh:mm:ss
Look like this:
mm/dd/yyyy hh:mm --hh:mm
My reply included a solution. I could have provided several solutions but chose the one with the least code. I am sorry that the CONVERT function is such a source of anxiety for you.
-- Itzik Ben-Gan 2001
July 12, 2012 at 7:47 am
This would make the trick. But as noted by others, formatting should be done on the front end.
DECLARE @Date datetime
SET @Date = '20120523 18:03:12.523'
SELECT CONVERT( char(10), @Date, 101) + ' ' + CONVERT( char(5), @Date, 8)
July 12, 2012 at 8:08 am
There's a whole series of date manipulation examples on Robyn Page's site: http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/
A little out of date now, but still useful.
July 12, 2012 at 8:34 am
sqluser_8119 (7/11/2012)
How do you make this:yyyy/mm/dd hh:mm:ss
Look like this:
mm/dd/yyyy hh:mm --hh:mm needs to be in military time
You are getting a lot of responses, but the biggest problem is that we are shooting in the dark as we have no idea how, where, or why you are trying to accomplish this in SQL. How about giving us some more information so we can offer better advice.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply