Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • 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
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.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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