Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Basic SQL - Date

Posted on 2004-10-26
7
Medium Priority
?
1,200 Views
Last Modified: 2012-05-05
I have a numeric field containing a date formated as CYYMMDD(C = Century, if C=1 it is 2000, if C=0 it is 1900). Some dates are :-
1040915
1040915
1040915
1040917
1040917
1040428

I want t display it as MM-DD-YYYY.
 This works for me but I feel the formating is too long.

select CONVERT(CHAR(10), CONVERT(DATETIME, SUBSTRING(LTRIM(STR(A.DateCreated)),2,6), 112), 110) 'Date Created'
from Order A

some result
09-15-2004
09-15-2004
09-15-2004
09-15-2004
09-15-2004
04-28-2004
08-18-2004
08-18-2004
08-18-2004
09-15-2004
09-15-2004


If you have a better Approach let me. If you think I am doing the right thing let me know too. I will award the points either way.

Thanks
0
Comment
Question by:yassin092898
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 8

Accepted Solution

by:
SashP earned 300 total points
ID: 12412255
Forget the formatting.

The issue is performance.

If you are happy with the performance and the code works stick with it.  Do not over engineer a solution as you may be taking time away from other important issues.

If it works (performance issues not withstanding) do not fix it.

Cheers Sash
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 300 total points
ID: 12412640
Personnally i'd create a new computed column on the table, in true datetime format

alter table Order add DateTime_Created as cast(cast(19000000 + @day as varchar) as datetime)

then i'd use this column to make all sorts of things that can't be achieved with your above format :

- retrieve records that are less than 10 days old
select * from Order where datediff(day, DateTime_Created, getdate()) < 10

- calculate a difference in days between two records
- retrieve week numbern, month name, ....
date functions are quite powerful in SQL Server, it's really a shame not to use datetime format

As for the formatting, it should be handled on the front-end side.

HTH

Hilaire

0
 
LVL 8

Expert Comment

by:SashP
ID: 12416835
Your function does not work when the century = 0.

So "if it aint broke don't fix it" does not seem to apply here.

Have a look at what happens when you get dates in the 1900s


declare @a table (adate int)

insert into @a values('1040915')
insert into @a values('1990915')
insert into @a values('1040915')
insert into @a values('1040917')
insert into @a values('0000917')
insert into @a values('0990421')

select * from @a

select STR(adate) from @a

0
 
LVL 8

Expert Comment

by:SashP
ID: 12416847
Then look at

declare @a table (adate int)

insert into @a values('1040915')
insert into @a values('1990915')
insert into @a values('1040915')
insert into @a values('1040917')
insert into @a values('0000917')
insert into @a values('0990421')

select * from @a

select case when adate > 999999 then '20' else '19' end +  right('000' + ltrim(STR(adate)),6) from @a
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question