date convertion - 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

date convertion

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-02-28 : 02:10:20

can anybody tell how to convert the date into the below format
datetime 2007-12-01 00:00:00.000
To 20071201

jobejufranz
Starting Member

33 Posts

Posted - 2008-02-28 : 02:35:22

quote:
Originally posted by sent_sara

can anybody tell how to convert the date into the below format
datetime 2007-12-01 00:00:00.000
To 20071201

hi, how about

CONVERT(VARCHAR,YEAR(tDate)) + CONVERT(VARCHAR,MONTH(tDate))+ CONVERT(VARCHAR,DAY(tDate))

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-28 : 04:07:27

Why are you formatting date in sql? As often said from people on this forum, do not do conversion in sql, do them in front end. use sql to get raw data that you want , not formatted data that you want. format in front end.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 04:23:27

sent sara

Look up CONVERT in BOL for possible values to format your date. Here is the one that will give you the desired output, i.e. YYYYMMDD


declare @theDate datetime

set @theDate = getdate()

select convert(varchar, @theDate, 112)

_____________________________________________________

sunil

Don't be so patronising! Take it easy please.

Not all formatting must be done in the front end! There are time when formatting is required in the backend also. How about when you have a DATE dimension table? Or what if you want to use sqlcmd to output a file with the date postfixed to the file name in this particular format?!

I'm not having a go or anything but you shouldn't make blanket statements like that. It's a little off putting IMHO.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 04:33:23

Maybe SQL is the front end? What if the date is to be exported to a file?

Use SELECT CONVERT(CHAR(8), GETDATE(), 112)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 04:33:57

too late for me!

E 12°55'05.25"
N 56°04'39.16"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 04:39:09

Not being the biggest fan of new age science, but what other explanation can someone give for 2 people thousands of miles away experiencing the same thought. One word.... SQL!

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-02-28 : 04:56:34

thank u MR ABU-DINA ITS WORKING FINE

quote:
Originally posted by Abu-Dina

sent sara

Look up CONVERT in BOL for possible values to format your date. Here is the one that will give you the desired output, i.e. YYYYMMDD

declare @theDate datetime

set @theDate = getdate()

select convert(varchar, @theDate, 112)
_____________________________________________________

sunil

Don't be so patronising! Take it easy please. Not all formatting must be done in the front end! There are time when formatting is required in the backend also. How about when you have a DATE dimension table? Or what if you want to use sqlcmd to output a file with the date postfixed to the file name in this particular format?!

I'm not having a go or anything but you shouldn't make blanket statements like that. It's a little off putting IMHO.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-28 : 05:17:12

quote:
Originally posted by Abu-Dina

sent sara

Look up CONVERT in BOL for possible values to format your date. Here is the one that will give you the desired output, i.e. YYYYMMDD

declare @theDate datetime

set @theDate = getdate()

select convert(varchar, @theDate, 112)
_____________________________________________________

sunil

Don't be so patronising! Take it easy please. Not all formatting must be done in the front end! There are time when formatting is required in the backend also. How about when you have a DATE dimension table? Or what if you want to use sqlcmd to output a file with the date postfixed to the file name in this particular format?!

I'm not having a go or anything but you shouldn't make blanket statements like that. It's a little off putting IMHO.

I am not patronising

. My statement was after reading so many replies from people on this forum (who ofcourse knows sql better than me). I have learned most of my SQL by reading replies to post made here and best practices people ask to follow.Can not remeber person who says "SQL is not made for giving formatted results, it is best used for fetching raw data." Also, i have not come across any scenarios you have mentioned. You may say that my blanket statement was based on my experience here on SQLTEAM.Please excuse me if made a wrong shot too to the post.

jobejufranz
Starting Member

33 Posts

Posted - 2008-02-28 : 05:29:36

quote:
Originally posted by sunil

Why are you formatting date in sql? As often said from people on this forum, do not do conversion in sql, do them in front end. use sql to get raw data that you want , not formatted data that you want. format in front end.

Hi sunil,

Since i'm new in this forum, may I ask why we should not do conversion in sql? Is there any performance penalty?

Honestly, I'm a web dev and I let our SQL Server convert our date Data into what we actually need. I pull out thousand records per transactions.

I previously do a conversion on our front-end but it is too much slow. Imagine you have to iterate through thousands of records and convert them and then show this result into your client?

Correct me if I'm wrong but i just want to clarify something. Maybe there are other ways to do this.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 05:46:53

quote:
I am not patronising . My statement was after reading so many replies from people on this forum (who ofcourse knows sql better than me). I have learned most of my SQL by reading replies to post made here and best practices people ask to follow.Can not remeber person who says "SQL is not made for giving formatted results, it is best used for fetching raw data." Also, i have not come across any scenarios you have mentioned. You may say that my blanket statement was based on my experience here on SQLTEAM.Please excuse me if made a wrong shot too to the post.

I totally understand what you mean. The aim is for all of us to keep learning. Not a day goes by without me learning something about this great technology. Long it may continue.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-28 : 05:51:30

quote:
Originally posted by jobejufranz

quote:
Originally posted by sunil

Why are you formatting date in sql? As often said from people on this forum, do not do conversion in sql, do them in front end. use sql to get raw data that you want , not formatted data that you want. format in front end.

Hi sunil,

Since i'm new in this forum, may I ask why we should not do conversion in sql? Is there any performance penalty?

Honestly, I'm a web dev and I let our SQL Server convert our date Data into what we actually need. I pull out thousand records per transactions.

I previously do a conversion on our front-end but it is too much slow. Imagine you have to iterate through thousands of records and convert them and then show this result into your client?

Correct me if I'm wrong but i just want to clarify something. Maybe there are other ways to do this.

I don't have the link to the article. Will try to search it and provide you the same. What I can recall from article is that "People often use T-SQL for purposes that can be easily done in front end. T-SQL should be used to fetch data not formatted data.". There may be cases as Abu-Dina said, you may need to do formatting in SQL. I am also a web dev and have avoided doing any formatting in SQL since without any performance problems. Hope some one throws some light as to why to do formatting in SQL if there is front end to do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 06:02:06

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862

Madhivanan

Failing to plan is Planning to fail

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 06:12:12

quote:
Originally posted by madhivanan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862

Madhivanan

Failing to plan is Planning to fail

What an excellent thread! Should be made a sticky if it isn't already.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 06:15:57

quote:
Originally posted by Abu-Dina

quote:
Originally posted by madhivanan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862

Madhivanan

Failing to plan is Planning to fail

What an excellent thread! Should be made a sticky if it isn't already.

That is one of the reasons why I always reply "If you use front end application, do formation there"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources