Dates

I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
Thanks.Look up convert in the Holy Book (SQL Server Books Online)|||Several possibilities:
1. Write a scalar UDF that returns a date time from a set string format.

2. USe the following T-SQL (though it may be slow):

declare @.DateString varchar(8)

select @.DateString = '20030731'

select
cast(substring(@.DateString, 5, 2) + '/' + substring(@.DateString, 7, 2) + '/' + substring(@.DateString, 1, 4) as DateTime)

3. If you are importing this date into your database from another data source using DTS, you can use on of the Copy options to specify that the source is a date/time string (and then specify the precise format).

Regards,

Hugh Scott
Originally posted by exdter
I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
Thanks.|||Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

Regards,

hmscott

Originally posted by Enigma
Look up convert in the Holy Book (SQL Server Books Online)|||Thats what I was afraid of. Orqacle makes it so easy.
Thanks for your time.|||I just re-read your sig line. I nearly spit coffee all over the keyboard. Thanks for starting my day off with a laugh.

:-)

Originally posted by Enigma
Look up convert in the Holy Book (SQL Server Books Online)|||Why? And I'm glad you were amused.|||Originally posted by hmscott
Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

Regards,

hmscott

how about

select convert(varchar,convert(datetime,'20031201'),101)|||I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.|||Originally posted by exdter
I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.

Use

select convert(varchar(10),convert(datetime,column_name), 101)|||This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.|||Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott|||Try this:

declare @.DateString varchar(8)

select @.DateString = '20030731'

select convert(datetime, @.DateString)

Originally posted by hmscott
Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott|||I'm not sure I follow you. The string I have is in number format and is in the 'yyyymmdd' format.|||I need to put a column name there.|||This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

In case you are receiving that error , there is surely some value which does not fit in into the yyyymmdd format. You will need to correct tahat first.|||Sorry, try this:

/* begin DDL */
CREATE TABLE DateNumbers (
DateNumber int
)
GO

INSERT INTO DateNumbers VALUES (20030731)
GO
INSERT INTO DateNumbers VALUES (20030801)
GO
INSERT INTO DateNumbers VALUES (20030802)
GO

SELECT Cast(Cast(DateNumber as Varchar(8)) as datetime) FROM DateNumbers

I did not understand that the field was numeric.

Regards,

hmscott
Originally posted by exdter
I need to put a column name there.|||I have 15000 dates in the table. I need to use a column name.
Thanks|||Originally posted by hmscott
Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott
from the Holy book again

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

the style values used converting datetime to varchar work the other way round too
eg : select convert(datetime,'12/01/2003',103)
style 103 : dd/mm/yy (British/French)|||If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null|||It's because I mis-read your post the first time. You should use this function here (which casts the numeric to a string before passing it in to be cast as a datetime).

SELECT Cast(Cast(DateNumber as Varchar(8)) as datetime) FROM DateNumbers

Regards,
hmscott

Originally posted by exdter
If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null|||Originally posted by exdter
If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null

Well .. as i said before

quote:
------------------------

This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

------------------------

In case you are receiving that error , there is surely some value which does not fit in into the yyyymmdd format. You will need to correct tahat first.|||I get this
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Type column_name is not a defined system type.
I used this SELECT Cast(Cast(column_name as Varchar(8)) as datetime) FROM table_name|||Originally posted by exdter
I get this
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Type column_name is not a defined system type.
I used this SELECT Cast(Cast(column_name as Varchar(8)) as datetime) FROM table_name

well lets see ...
try this

select * from your_table where ((substring(your_column,1,4) < '1753' or substring(your_column,1,4) < '9999' or substring(your_column,5,2) <'01' or substring(your_column,5,2) > '12' or substring(your_column,7,2) <'01' or substring(your_column,5,2) >'31' )

and see if you get any rows|||The column is in number form and substring works for char from.|||see if you can take a bcp out for the particular column and post it here so we can work on it|||Sorry, I don't know what bcp is.|||Run " select column_name from table_name" in Query analyzer.
Select the results ...
copy into text file and post here ..|||This is just a sample of the column. Is this ok?
20020731
19990423
19990607
19960903
19980402
20010718
19930419
20000101
19960329
19950109
20000630
19970815
20010118
20001205
19960306
19991116
19960313
19930719
19910502
20000509
20010926
20011106
20000517
19950525
19981029|||select convert(datetime, convert(varchar,20031120)) as xxx

You can replace 20031120 by the actual column name.

If you want it to be a string, you can further convert datetime into char.|||This works for this instance, but i need to do this for a whole column. If I put the column name, I get an error.
Thanks.|||exdter ...
we would need the complete data to point out where the error is ..|||I checked the data. I put a clause where column_name>0 and column_name is not null. The data that appears is all in the same format as what I posted. It goes 'yyyy/mm/dd'|||select * from your_table
where (
(substring(convert(varchar(8),your_column),1,4) < '1753'
or substring(convert(varchar(8),your_column),1,4) < '9999'
or substring(convert(varchar(8),your_column),5,2) <'01'
or substring(convert(varchar(8),your_column),5,2) > '12'
or substring(convert(varchar(8),your_column),7,2) <'01'
or substring(convert(varchar(8),your_column),7,2) >'31' )

Does this return any row ?|||Try this:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0

That should help identify bad data.

blindman|||thanks blindman ...
that was exactly what i was searching for

time to get back to the holy book :)|||I put in my query 'where column_name>0'
Thanks for your help.|||If I put
set dateformat ymd
go
select cast(column_name as smalldatetime)
from table_name
where column_name =0
go

It works. But it only works on the zeros. Otherwise, if I put
and column_name>0 then I get the error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.|||Select * from tablename where Isdate(cast(columnname as char(8))) = 0

does this return any results...?|||It does. Thats why in my convert query I added
where column_name>0|||OK, so what about

Select *
from tablename
where Isdate(cast(column_name as char(8))) = 0
and column_name>0

blindman|||I get no results. I did:

column_name=0
column_name is null
len(column_name) !=8

and got no results for any of them
I even physically went through and looked at the dates and they were all fine.|||Originally posted by exdter
I get no results. I did:

column_name=0
column_name is null
len(column_name) !=8

and got no results for any of them
I even physically went through and looked at the dates and they were all fine.

I also ran

select isdate(column_name)
from table_name

and got results that the column can be converted to a date.|||Wow .. this has become the biggest thread of all times ...43 posts !!!

exdter ... can you post the exact query you are running on your machine that is returning an error. Also can you give the result of the query

Select * from tablename where Isdate(cast(columnname as char(8))) = 0|||For the query:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0 , I get no results. Meaning there are no zeros. I picked a different table where there are no zeros.|||Originally posted by exdter
For the query:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0 , I get no results. Meaning there are no zeros. I picked a different table where there are no zeros.

When I run

select convert(char(10),column_name,101)
from table_name

I just get the same string back as what is already there. The numeric string. For example 20030705
I really appreciate your help on this.

I am also looking at

select left(column_name,4) + '/' + right(column_name,2) + '/' --+ right(column_name,3)
from table_name.

I got it to look like 2002/24/ so far.|||select convert(varchar(10),convert(datetime,column_name), 101)
from table_name where isdate(cast(columnname as char(8))) = 1|||Syntax error near 'as'|||Originally posted by exdter
Syntax error near 'as'
OOPS ...

select convert(varchar(10),convert(datetime,column_name), 101)
from table_name where isdate(convert(varchar(8),columnname)) = 1|||Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

And I know that all the strings can be converted because I ran
isdate(column_name) and got all 1's.|||Can you post the ddl for the table ?

Am running out of ideas :(|||Sorry, whats the ddl?|||I mean the SQL script for creating the table.|||Ya know...

CREATE TABLE myTable99 (Col1 int, ect...

AND Sample Data...

INSERT INTO myTable99(Col, ect..
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect

Would help us a lot...|||CREATE TABLE datestimes (firmfile varchar(20),orddate int(4))
AND Sample Data...

INSERT INTO datestimes(firmfile '03000004',orddate 20030724)

Thats all it is. There are about 15000 rows and all the firmfiles are just our folder numbers. And then there is the orddate which is the date the order was put in. All the orddate data is in the format 'yyyymmdd'
I checked this.
Is this enough?
Thanks alot.|||Try zeroing in on the problem:

select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19000101 and 20040101

This will give an error. So then try:
select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19900101 and 20040101

Still get the error? Try:
select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19950101 and 20040101

Get the idea?

blindman|||I get an arithmetic error all the way up to today.|||What if you hardcode a sample value from your recordset?:

select convert(datetime,cast(20031011 as varchar(8)))
from table_name

blindman|||It works.|||How about doing a

bcp yourdatabase.ownername.datestimes out c:\datestimes.txt -c -T -a 65535

on your server at command prompt and posting the file over here.|||I can't. Its stuff that can't leave here.|||No problems mate

USE Northwind
GO

CREATE TABLE datestimes (firmfile varchar(20),orddate int)
GO

INSERT INTO datestimes(firmfile,orddate )
SELECT '03000004', 20030724
GO

Can you cut and paste that in to QA and see if it runs?

Did s/he sday that this sql server...if it's mySQL...I ougtta...

bang...zoom..|||Right to the moon, Alice...

Ok the table is made.
I still get the same results.

select convert(char(10),column_name,101)
from table_name

For this, it returns the original string.|||Is this SQL Server ? If it is , can you tell us the version

select @.@.version|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)|||Originally posted by exdter
Right to the moon, Alice...

Ok the table is made.
I still get the same results.

select convert(char(10),column_name,101)
from table_name

For this, it returns the original string.

But that's not what I posted...

Did you cut and paste what I posted in to Query Analyser?

Did it fail?

I don't believe it...

The other thing is what does DBCC CHECHTABLE(datestimes)

Tell you?|||I cut and pasted it to pubs. There were no error messages on the DBCC CHECKTABLE(datestimes). I assume you meant checKtable as you put checHtable|||Originally posted by exdter
I cut and pasted it to pubs. There were no error messages on the DBCC CHECKTABLE(datestimes). I assume you meant checKtable as you put checHtable

damn hangover...

You cut and pasted it in to Pubs...and..it worked/didn't work?

works for me...

And you did the DBCC against the table you're having the problem with correct?|||Yes to all.|||Originally posted by Brett Kaiser
You cut and pasted it in to Pubs...and..it worked/didn't work?


[beating dead horse repeatedly]
But it's not a yes or no question...
[/beating dead horse repeatedly]

[:-)]|||I cut and pasted into pubs and the table was made. When I try to do the conversions, I get the same replies as on the tables I am trying to do the conversion.|||Ok, wait...and this code...sorry

select convert(datetime,cast(orddate as varchar(8)))
from datestimes
where orddate between 19950101 and 20040101

Does that run in Pubs?|||Works like a charm. The date appears as I want it to.|||I put that on my original table and it WORKED!!! THANKS!!!!!

I see. I used this:
select convert(cast(orddate as varchar(8)))
from datestimes

You gave me this:
select convert(datetime,cast(orddate as varchar(8)))
from datestimes
I didn't have DATETIME,cast in mine.|||[smacking head with hand]
That's what blindman gave a couple of hours ago
[/smacking head with hand]

I was trying to give a whole snippet of code to run, and left off the select

Look up BETWEEN in BOL, but it basically does what it says, inclusively.

NEXT!|||...because you have bad date values either less than 19950101 or greater than 20040101. You need to find them. Try the zeroing in method again.

blindman|||Now I'm totally lost. I put the queries in that you gave me to try and zero in and they all work now. I do it without the 'between'. I'm not making this up. It doesn't matter. At least its worked out. Thanks to all.|||Or you can say in the WHERE Clause

select convert(datetime,cast(orddate as varchar(8)))
from datestimes
where orddate between 19950101 and 20040101
and ISDATE(OrdDate) = 1

NEXT?|||It works without the between now. I have no idea why. Anyway, thanks!!!|||Brett... Still need another one ?|||Voodoo and dead chickens.