[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Basic SQL

Posted on 2004-10-21
7
Medium Priority
?
363 Views
Last Modified: 2012-08-14
I have a database field of type numeric 5 containing a number representing a date such as:-
 1040915
 1040915
What kind of date is this and how can I convert to a regular date of MMDDYYYY?
It looks like 1YYMMDD.

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
7 Comments
 
LVL 6

Accepted Solution

by:
robertjbarker earned 400 total points
ID: 12377506
declare @date varchar(7)

set @date = '1040915'

select convert (datetime,substring(@date,2,6),112)

or just plain

select convert (datetime,substring'1040915',2,6),112)
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 12377511
sorry missing (, should be:

select convert (datetime,substring('1040915',2,6),112)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12377683
Hello yassin!

Basing from your posted question, for example you have the following records on your table:
<<< YourTable >>>
----------
Column1
----------
1040915
1041015
1041115

Try running this query :

Select DateFormat(convert(date,substring(Column1,2,6),12),'mmddyyyy') as NewDate
From YourTable

This would be the result :
----------
NewDate
----------
09152004
10152004
11152004

Hope this helps.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:yassin092898
ID: 12379979
thank you both. I am using SQLServer 2000 but

Select DateFormat(convert(date,substring(Column1,2,6),12),'mmddyyyy') as NewDate

is giving me this error
'DateFormat' is not a recognized function name.

I just want to know if
entrance2002 is using MS SQLServer 2000.
thanks
0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 12386843
There is a DateFormat in MS SQLServer 2000, but I don't think it is particularly useful in this situation. And it is not used in a select statement.

Its use is to set the order of the month, day and year in a date, in a pretty restricted way.  From books on-line an example is:

SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 12388235
try:

Select convert(date,substring(Column1,2,6),112) as NewDate
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 12388239
or
Select convert(date,'20'+substring(Column1,2,6),112) as NewDate
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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