Change the date into user format

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

SQL Server 2005 Forums

Transact-SQL (2005)

Change the date into user format

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:01:25

Hi everyone,

I need to change the date in below format -
dd/mm/yyyy hh:mm:ss PM

take getdate() for example.

Can anybody help me as there is not any in built format in sql like this.

Thanks in advance

Regard,
Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:10:52

you need to do this at your front end as it is a formatting issue. Make use of formatting functions in your application language.
Just in case you dont have a front end, make use of CONVERT function in sql.

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-16 : 02:13:01

Try this!

select convert(varchar,getdate(),103)+' '+ right(convert(varchar,getdate(),100),7)

Better done it in front end!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:15:26

actually from the backend i am giving the result by
concatenating the date in that format with some strings

so it is being difficult to make it saperate for front end guy.

so i need to format it in backend but how to use that convert function
because there is no such date format in the allowed range 100 to 114.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:16:59

quote:
Originally posted by vaibhavktiwari83

actually from the backend i am giving the result by
concatenating the date in that format with some strings

so it is being difficult to make it saperate for front end guy.

so i need to format it in backend but how to use that convert function
because there is no such date format in the allowed range 100 to 114.

this is again a formating solution which can very easily be done at your application level

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:18:27

senthil

your query very near to that format but i need seconds also.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:20:35

visakh16

You are right but some complexities are there at front end level.
so that will be another thing to discuss.
thanks
Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:24:34

quote:
Originally posted by vaibhavktiwari83

visakh16

You are right but some complexities are there at front end level.
so that will be another thing to discuss.
thanks
Vaibhav T

ok here you go then..


select convert(varchar(11),getdate(),103)+' '+ right(convert(varchar,getdate(),109),15)

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-16 : 02:27:36

quote:
Originally posted by vaibhavktiwari83

senthil

your query very near to that format but i need seconds also.

select convert(varchar,getdate(),103)+' '+right(convert(varchar,getdate(),9),13)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:29:21

visakh16

Its again giving me miliseconds which i dont want as i post the format
dd/mm/yyyy hh:mm:ss PM

Regards,
Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:30:59

quote:
Originally posted by senthil_nagore

quote:
Originally posted by vaibhavktiwari83

senthil

your query very near to that format but i need seconds also.

select convert(varchar,getdate(),103)+' '+right(convert(varchar,getdate(),9),13)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Senthil please check query before posting. hour part will get cut off in your query.

see


--Visakh
select convert(varchar(11),getdate(),103)+' '+ right(convert(varchar,getdate(),109),15)
--Senthil
select convert(varchar,getdate(),103)+' '+right(convert(varchar,getdate(),9),13)


output
-------------------------
--Visakh
16/02/2010  12:59:51:903PM
--Senthil
16/02/2010 2:59:51:903PM

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

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 02:38:41

I think you guys should explicitly put a size on you VARCHAR in those convert statements. I know the conversion is not going to exceed the default, but I still think its a good idea ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:42:31

quote:
Originally posted by vaibhavktiwari83

visakh16

Its again giving me miliseconds which i dont want as i post the format
dd/mm/yyyy hh:mm:ss PM

Regards,
Vaibhav T


select convert(varchar(11),getdate(),103)+' '+ stuff(right(convert(varchar,getdate(),109),15),10,4,' ')

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:52:20

Thats wonderful thats what i want.
thank you very much visakhm.

i was doing something very long manipulation but that very good and much clear.

Regards,
Vaibhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-16 : 02:57:30

I was doing something like

SELECT REPLACE ( CONVERT(VARCHAR(10), GETDATE(), 103) + SUBSTRING( CONVERT(VARCHAR, GETDATE(), 109), 12, 15),
SUBSTRING(CONVERT(VARCHAR, GETDATE(), 109), 21, 4) , ' ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 03:11:32

quote:
Originally posted by vaibhavktiwari83

Thats wonderful thats what i want.
thank you very much visakhm.

i was doing something very long manipulation but that very good and much clear.

Regards,
Vaibhav T

welcome

But it certainly is not my recommended approach. I would be trying to accommodate this formatting at front end as far as possible

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-16 : 03:55:41

quote:
Originally posted by Kristen

I think you guys should explicitly put a size on you VARCHAR in those convert statements. I know the conversion is not going to exceed the default, but I still think its a good idea ...

Many people often forget it
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 04:01:36

quote:
Originally posted by madhivanan

quote:
Originally posted by Kristen

I think you guys should explicitly put a size on you VARCHAR in those convert statements. I know the conversion is not going to exceed the default, but I still think its a good idea ...

Many people often forget it
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

i just copy pasted it from OPs initial response.
I myself always make it a point to specify length
Sorry I missed spotting it

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-16 : 04:04:53

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by Kristen

I think you guys should explicitly put a size on you VARCHAR in those convert statements. I know the conversion is not going to exceed the default, but I still think its a good idea ...

Many people often forget it
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

i just copy pasted it from OPs initial response.
I myself always make it a point to specify length
Sorry I missed spotting it

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

I know it

You are not in that Many People

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-16 : 04:08:20

cool, I will follow it in future!

Thanks to spot it.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 04:12:00

Well now I will admit something

I always use

CONVERT(varchar(20), SomeNumber)
CONVERT(varchar(24), SomeDate, nnn)
CONVERT(varchar(50), SomeGUID)

because I figure that 20 is enough for a number, 24 for a date, and 50 for a GUID because I can never remember how long they are!

Sloppy, I know ...

... however, usually "safe"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources