Datetime Conversion - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Datetime Conversion

Author

Topic

matticusfinch
Starting Member

9 Posts

Posted - 2005-09-02 : 09:18:03

I have a text field for Transaction Date that I need to convert to datetime or smalldate time for sorting capabilities in my application.
However, I want the date only, not the time so it looks cleaner. If I trim the field, it converts back to varchar. I can't figure out a way to get the date only. Is this possible??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 09:24:52

Why did you use varchar or text datatype to store Dates?
Use proper Datetime datatype

You need to use convert function like this

Declare @t varchar(20)
set @t='2005-01-01 12:12:22'
select convert(varchar,convert(datetime,@t) ,101)

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-02 : 09:28:23

You really should store the concept of a transaction date in a datetime (or smalldatetime) column. Then present that date in any format you want in your presentation layer. If you don't want to format the date in your presentation layer you can return a converted to varchar date value in any format you want and still sort by the datetime column. (you can sort by a column that is not returned in the result set).

ie:
select convert(varchar,TransDate,101) from myTable order by TransDate

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 09:32:40

I have read many questions like this to convert date to datetime when it is stored in varchar datatype. I wonder what is the advantage of not using datetime datatype

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-02 : 09:38:46

I've noticed the same thing. I believe "dates" stored as anything but datetimes are virtually useless. I think a lot of the time people are converting the data from other systems and just don't understand the sql ramifications of non-datetime datatypes (or don't understand the benifits of converting to datetime)

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-02 : 12:13:33

[ dead horse]
*kick kick kick kick*
PRESENTATION LAYER!!!!!!!1!!!!!!111!!!!eleven!!!
*kick kick kick kick*
[ /dead horse]

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-09-02 : 14:00:08

To DonAtWork: Why you post not related messages in this forum?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 01:39:54

quote:
Originally posted by DonAtWork

[ dead horse]
*kick kick kick kick*
PRESENTATION LAYER!!!!!!!1!!!!!!111!!!!eleven!!!
*kick kick kick kick*
[ /dead horse]

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

What will you do if presentation layer does not support conversion?
Why does SQL Server have Cast and Convert Functions?

Madhivanan

Failing to plan is Planning to fail

matticusfinch
Starting Member

9 Posts

Posted - 2005-09-06 : 04:07:39

Point well taken and choir has been effectively preached at.
Unfortunately, I'm dealing with a vendor's database in which they laugh at conventional wisdom and like to make it difficult for data-miners as myself.

Thanks for the input, though...

matticusfinch
Starting Member

9 Posts

Posted - 2005-09-06 : 04:13:21

Problem with converting to varchar is the sorting, though.
Sorts as 1, 10, 2, (or Aug, July, June for that matter)etc, causing ineffective result sets.
This is the reason I didn't just use a LEFT command, to get rid of the time.
SO, the end goal is to get it in a sortable format......
I'm frustrated!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-06 : 04:21:42

Try this

Select Columns from yourTable Order by len(yourField), yourField

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-06 : 05:06:54

play around with this and see if you can make something out of it, without data, I'm just guessing what your table and data looks like

declare @t table(col1 int identity(1,1),dateval datetime)

insert into @t(dateval)
select getdate()
insert into @t(dateval)
select getdate() -1
insert into @t(dateval)
select getdate() +1

select col1,convert(varchar(10),dateval ,101) from @t
order by dateval

--------------------
keeping it simple...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-06 : 09:10:53

This varchar format should sort correctly: yyyymmdd

select convert(varchar, getdate(), 112) --(this format includes leading zeros)

Another possibility could be to create a new associated table with PK of your vendors table and the datetime value.

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-06 : 10:43:33

"What will you do if presentation layer does not support conversion?"
I dont think i have ever seen a presentation layer that does not allow you to manipulate the way something is presented.

"Why does SQL Server have Cast and Convert Functions?"
So you can convert the crap that a front end passes to you into something you can store correctly?

"To DonAtWork: Why you post not related messages in this forum?"
It relates DIRECTLY to the question asked.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-06 : 13:32:31

quote:
Originally posted by madhivanan
What will you do if presentation layer does not support conversion?
Why does SQL Server have Cast and Convert Functions?

Better question, right back at you: Why *doesn't* SQL have formatting functions to let your format your dates and numbers?

Conveting datatypes is a DATA issue, so SQL needs a way to do it. Databases should deal with DATA. (now that's a crazy concept

) Formatting data is NOT a database issue.

My biggest gripe with T-SQL is that there are not simple functions that allow you to construct a date w/o using a conversion, or a simple function to strip the time off a date -- both of which, again, deal with data and not formatting.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 02:45:13

Well Jeff
Once I had requirement to copy data to text file from SQL Server table where I needed to show the Ordered_Date in "dd-mmm-yyyy" format. I didnt find any way other than using Convert Function

But Selecting date using convert function does not make major impact on the performance



Set nocount on
Create table #date_test(d datetime primary key)
Declare @i int
set @i=1
While @i<=1000
Begin
	Insert into #date_test values(getdate()+@i)
	set @i=@i+1
End
Select d from #date_test
Select d from #date_test where convert(varchar,d,112)<='20100101'
Select convert(varchar,d,113) from #date_test
select replace(convert(varchar,d,106),' ','-') from #date_test

Drop table #date_test
Set nocount off

The Execution plan says query cost of all the above four queries is 0.22%

I am not expert in SQL Server or any other databases so I may be wrong

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-08 : 10:13:49

>>Once I had requirement to copy data to text file from SQL Server table where I needed to show the Ordered_Date in "dd-mmm-yyyy" format. I didnt find any way other than using Convert Function

>>But Selecting date using convert function does not make major impact on the performance

Not sure what you are saying. SQL Server doesn't "show" anything -- that's the whole point. It returns data. To "show" something in a particular format, you need a client application. And that's where you should worry about formatting the date.

Remember (and I feel like a broken record sometimes but it is very important) when you return things formatted and converted to varchars in SQL Server, you are NOT returning date values! You are now returning strings. They don't sort the same, compare the same, or work the same as real date VALUES. For a client to use these nicely formatted strings as a true date, it needs to immediately convert those strings BACK to a true datetime datatype!

The issue is not whether or not it is efficient to convert dates to varchars, but the issue is whether or not you should do it in the first place and what you end up returnig when you do convert things. And that is why, to answer the question I asked, SQL Server does NOT have formatting functions.

One of the most important distinctions you need to make when working with databses is the difference between the DATA itself and taking that DATA and presenting it, formatting it, totalling it, and so on, and which tool should be doing which part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 10:29:32

Good points Jeff

>>Not sure what you are saying

I meant that following does not make any performance issue until Convert is used in Where Clause

Select DateCol from myTable
Select Convert(varchar,DateCol,106) from myTable

In some cases where I need to export data to file (say notepad), I use Convert function on Date column to format it the way I want. As It is not possible to change the format at Notepad and there is no need of comparing or calculating the converted date fields, I think there is nothing wrong to use Convert function to format the date to the way I want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources