query for datetime - 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

query for datetime

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-01 : 03:48:28

hi friends,

I need to display the date in the format "day Month date,Year".
i used the below query for today's date,its working fine. if i need to use some other date its not working.
SELECT DATENAME(dw,GETDATE()) + ' ' + REPLACE(CONVERT(varchar(12),GETDATE(),107),LEFT(DATENAME(mm,GETDATE()),3),DATENAME(mm,GETDATE()))

For some other date i used this query.But not working
SELECT DATENAME(dw,'1/1/2008') + ' ' + REPLACE(CONVERT(varchar(12),'1/1/2008',107),LEFT(DATENAME(mm,'1/1/2008'),3),DATENAME(mm,'1/1/2008'))

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-07-01 : 04:09:43

Dear Vidya, try this.....
select convert(varchar(50),getdate(),110)
go

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-07-01 : 04:21:54

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105764

Its better if you continue there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 04:29:27

quote:
Originally posted by vidhya

hi friends,

I need to display the date in the format "day Month date,Year".
i used the below query for today's date,its working fine. if i need to use some other date its not working.
SELECT DATENAME(dw,GETDATE()) + ' ' + REPLACE(CONVERT(varchar(12),GETDATE(),107),LEFT(DATENAME(mm,GETDATE()),3),DATENAME(mm,GETDATE()))

For some other date i used this query.But not working
SELECT DATENAME(dw,'1/1/2008') + ' ' + REPLACE(CONVERT(varchar(12),'1/1/2008',107),LEFT(DATENAME(mm,'1/1/2008'),3),DATENAME(mm,'1/1/2008'))

thats because when you pass an explicit value of date like '1/1/2008' it interprets it as of type varchar. it will convert the date to reqd format only if its in datetime. so if you want to pass explicit value of date and yet format it using CONVERT use like this

SELECT DATENAME(dw,'1/1/2008') + ' ' + REPLACE(CONVERT(varchar(12),CAST('1/1/2008' as datetime),107),LEFT(DATENAME(mm,'1/1/2008'),3),DATENAME(mm,'1/1/2008'))

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-01 : 05:47:04

thanks a lot visakh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-01 : 16:07:32

quote:
Originally posted by vidhya

hi friends,

I need to display the date in the format "day Month date,Year".
i used the below query for today's date,its working fine. if i need to use some other date its not working.
SELECT DATENAME(dw,GETDATE()) + ' ' + REPLACE(CONVERT(varchar(12),GETDATE(),107),LEFT(DATENAME(mm,GETDATE()),3),DATENAME(mm,GETDATE()))

For some other date i used this query.But not working
SELECT DATENAME(dw,'1/1/2008') + ' ' + REPLACE(CONVERT(varchar(12),'1/1/2008',107),LEFT(DATENAME(mm,'1/1/2008'),3),DATENAME(mm,'1/1/2008'))

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources