Convert Date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Convert Date

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2011-08-03 : 10:03:38

Hi All,

I need to convert 01/01/1900 01:01:00 into 01:01 AM please help

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-08-03 : 10:11:20

Have a play around with:

DECLARE @D DATETIME
SET @D = '01/01/1900 01:01:00'

SELECT @D
SELECT CONVERT(VARCHAR,@D,108)
SELECT CONVERT(VARCHAR,@D,109)

Neither of these will give you the AM/PM though so you could get creative and try something like

SELECT LEFT(CONVERT(VARCHAR,@D,108),5) + ' ' + RIGHT(CONVERT(VARCHAR,@D,109),2)

Its hardly an elegent solution but it will work if you can't think of anything else.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 12:42:27

what about


DECLARE @D DATETIME
SET @D = '01/01/1900 01:01:00'

select right(convert(varchar(20),@D,100),8)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-08 : 09:25:43

If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-08-09 : 02:55:23

AS suggested by madhivanan, try to do formation on front end application.

go through these links for date format it may useful for you.
http://www.sql-server-helper.com/tips/date-formats.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

robtop
Starting Member

9 Posts

Posted - 2011-08-09 : 08:25:28

Afternoon,

I have a similar issue to this but cant seem to get these solutions to work so must be ddoing something wrong.

Basically I'm trying to show a datefield (PolicyToDate) + 1 day as a text string just for the day and month e.g

Show 01/01/2011 as '01 2011'

I've tried several ways of doign this, none of which works exactly as I would like.

If anyone has any ideas how to do this properly it would be much appreciated.

Thanks a lot

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 09:53:01

quote:
Originally posted by robtop

Afternoon,

I have a similar issue to this but cant seem to get these solutions to work so must be ddoing something wrong.

Basically I'm trying to show a datefield (PolicyToDate) + 1 day as a text string just for the day and month e.g

Show 01/01/2011 as '01 2011'

I've tried several ways of doign this, none of which works exactly as I would like.

If anyone has any ideas how to do this properly it would be much appreciated.

Thanks a lot

Rob

just a matter of

SELECT DATENAME(mm,@date) + ' ' + DATENAME(yyyy,@date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-09 : 16:24:08

>> I need to convert 01/01/1900 01:01:00 into 01:01 AM please help.<<

Temporal data is an abstraction in SQL, not display strings. That was COBOL and mag tapes decades ago. The strings you posted do not even follow ISO-8601 display format (The only one allowed in ANSI/ISO Standard SQL).

In Standard SQL, there is an EXTRACT() function. In T-SQL we can use:

DECLARE silly_datetime DATETIME2(0);
SET silly_datetime = '1900-01-01 01:01:00';

CAST (silly_datetime AS DATE) = '1900-01-01'
CAST (silly_datetime AS TIME) = '01:01:00'

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

robtop
Starting Member

9 Posts

Posted - 2011-08-10 : 04:17:10

Thanks a lot Visakh, I had managed to get soemthign along those lines working but the problem I'm getting is the date text string i'm getting from this gives me 1 August & I need it to show 01 August otherwise it doesn't sort properly in the report I build from this table.

Any ideas?

Thanks a lot

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 07:25:26

for that you can just use like

SELECT RIGHT('0' + DATENAME(mm,@date),2) + ' ' + DATENAME(yyyy,@date)

but if you've a front end try to do this in it as its a presentation issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources