• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Basic SQL

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
yassin092898
Asked:
yassin092898
1 Solution
 
robertjbarkerCommented:
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
 
robertjbarkerCommented:
sorry missing (, should be:

select convert (datetime,substring('1040915',2,6),112)
0
 
Renante EnteraSenior PHP DeveloperCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
yassin092898Author Commented:
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
 
robertjbarkerCommented:
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
 
ala_frostyCommented:
try:

Select convert(date,substring(Column1,2,6),112) as NewDate
0
 
ala_frostyCommented:
or
Select convert(date,'20'+substring(Column1,2,6),112) as NewDate
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now