September 3, 2009 at 11:53 am
I've been working on this for hours now. I didn't expect it to take so much time.
I was given 3 tables. Two of them had DOB as NVARChar the other one had it as DateTime datatype. I'm trying to move data in all three tables into one new table. I think it is best to make them all DateTime datatype but I don't care which way we decide to go. I just need to get them all in the same field.
I read this thread from 2003. It made the most sense to me.
http://www.sqlservercentral.com/Forums/Topic18198-8-2.aspx?Highlight=nvarchar+datetime
I also read another but it looked a bit complicated.
I found all the records that didn't have real dates by executing this code:
Select * from studentcur where isdate(dateofbirth) = 0
I Updated them using this code:
Update dbo.StudentCur Set DateOfBirth = '01/01/30' where isdate(dateofbirth) = 0
But when I run
Select dateofbirth = Convert(datetime, DateOfbirth, 101) from studentcur
I get this error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Here is a list of all the dates. I even changed the 4-digit years to 2-digit years.
04/01/62
08/30/51
09/10/52
01/30/61
12/07/40
11/08/47
05/12/38
11/15/45
11/04/60
07/03/55
03/13/61
12/04/57
11/03/52
09/19/56
04/06/76
03/14/61
07/26/53
12/27/47
06/08/49
09/10/42
07/16/49
12/18/58
09/30/82
07/21/76
01/20/72
01/31/69
01/17/68
05/23/35
04/28/55
10/26/52
02/14/69
09/20/55
02/21/54
05/27/82
04/13/61
11/01/50
07/25/57
02/26/44
02/04/51
07/08/55
05/31/63
01/03/61
07/24/56
09/11/75
05/11/65
05/13/67
02/01/67
06/24/46
04/21/60
02/20/70
08/16/49
10/18/53
02/11/56
08/15/48
03/09/45
06/03/57
11/01/49
01/02/65
01/10/54
12/15/58
01/01/30
07/21/67
05/05/66
11/25/65
11/15/67
10/25/80
06/02/54
04/03/39
11/25/74
02/24/43
11/27/52
07/18/61
05/10/54
07/03/70
07/19/55
10/11/64
09/04/54
09/27/51
11/19/74
04/07/81
12/25/42
07/12/65
12/26/61
05/31/53
08/17/66
07/07/60
09/16/75
01/04/64
11/28/51
02/19/55
01/04/60
08/08/44
10/07/63
07/06/53
02/27/46
09/30/53
01/01/30
5/15/50
05/20/42
06/22/55
02/21/38
07/12/46
04/30/67
05/21/56
03/04/59
08/06/63
05/18/80
03/22/40
08/11/53
11/10/75
08/25/50
01/27/62
02/03/69
07/13/70
12/29/67
10/12/83
03/21/60
10/18/70
6/10/1971
05/27/82
02/22/54
01/06/53
04/23/33
06/09/60
05/19/87
06/25/57
10/22/58
12/05/54
05/27/61
01/30/41
03/19/52
12/11/54
06/02/56
09/27/47
11/23/71
12/02/58
05/05/84
03/14/54
05/07/46
12/03/39
10/08/73
02/27/65
08/25/42
11/17/55
10/10/40
08/05/57
08/19/63
02/16/51
03/06/78
11/10/58
03/04/55
01/31/59
11/20/57
01/01/30
09/28/53
01/01/30
11/27/74
03/25/67
06/18/62
04/30/59
07/11/62
05/06/36
06/06/66
01/22/60
07/04/40
05/27/79
07/24/70
04/05/70
04/28/67
10/24/74
06/05/40
08/15/71
07/05/41
08/02/48
11/10/73
11/22/81
02/11/81
11/29/75
01/01/30
09/18/65
06/07/75
01/08/43
11/08/47
05/21/45
04/21/54
07/28/47
01/08/48
10/21/53
05/19/52
01/12/48
08/10/54
01/03/33
09/09/53
08/12/54
01/28/33
04/01/62
08/30/51
09/10/52
01/30/61
12/07/40
11/08/47
05/12/38
11/15/45
11/04/60
07/03/55
03/13/61
12/04/57
11/03/52
09/19/56
04/06/76
03/14/61
07/26/53
12/27/47
06/08/49
09/10/42
07/16/49
12/18/58
09/30/82
07/21/76
01/20/72
01/31/69
01/17/68
05/23/35
04/28/55
10/26/52
02/14/69
09/20/55
02/21/54
05/27/82
04/13/61
11/01/50
07/25/57
02/26/44
02/04/51
07/08/55
05/31/63
01/03/61
07/24/56
09/11/75
05/11/65
05/13/67
02/01/67
06/24/46
04/21/60
02/20/70
08/16/49
10/18/53
02/11/56
08/15/48
03/09/45
06/03/57
11/01/49
01/02/65
01/10/54
12/15/58
01/01/30
07/21/67
05/05/66
11/25/65
11/15/67
10/25/80
06/02/54
04/03/39
11/25/74
02/24/43
11/27/52
07/18/61
05/10/54
07/03/70
07/19/55
10/11/64
09/04/54
09/27/51
11/19/74
04/07/81
12/25/42
07/12/65
12/26/61
05/31/53
08/17/66
07/07/60
09/16/75
01/04/64
11/28/51
02/19/55
01/04/60
08/08/44
10/07/63
07/06/53
02/27/46
09/30/53
01/01/30
5/15/50
05/20/42
06/22/55
02/21/38
07/12/46
04/30/67
05/21/56
03/04/59
08/06/63
05/18/80
03/22/40
08/11/53
11/10/75
08/25/50
01/27/62
02/03/69
07/13/70
12/29/67
10/12/83
03/21/60
10/18/70
6/10/1971
05/27/82
02/22/54
01/06/53
04/23/33
06/09/60
05/19/87
06/25/57
10/22/58
12/05/54
05/27/61
01/30/41
03/19/52
12/11/54
06/02/56
09/27/47
11/23/71
12/02/58
05/05/84
03/14/54
05/07/46
12/03/39
10/08/73
02/27/65
08/25/42
11/17/55
10/10/40
08/05/57
08/19/63
02/16/51
03/06/78
11/10/58
03/04/55
01/31/59
11/20/57
01/01/30
09/28/53
01/01/30
11/27/74
03/25/67
06/18/62
04/30/59
07/11/62
05/06/36
06/06/66
01/22/60
07/04/40
05/27/79
07/24/70
04/05/70
04/28/67
10/24/74
06/05/40
08/15/71
07/05/41
08/02/48
11/10/73
11/22/81
02/11/81
11/29/75
01/01/30
09/18/65
06/07/75
01/08/43
11/08/47
05/21/45
04/21/54
07/28/47
01/08/48
10/21/53
05/19/52
01/12/48
08/10/54
01/03/33
09/09/53
08/12/54
01/28/33
04/01/62
08/30/51
09/10/52
01/30/61
12/07/40
11/08/47
05/12/38
11/15/45
11/04/60
07/03/55
03/13/61
12/04/57
11/03/52
09/19/56
04/06/76
03/14/61
07/26/53
12/27/47
06/08/49
09/10/42
07/16/49
12/18/58
09/30/82
07/21/76
01/20/72
01/31/69
01/17/68
05/23/35
04/28/55
10/26/52
02/14/69
09/20/55
02/21/54
05/27/82
04/13/61
11/01/50
07/25/57
02/26/44
02/04/51
07/08/55
05/31/63
01/03/61
07/24/56
09/11/75
05/11/65
05/13/67
02/01/67
06/24/46
04/21/60
02/20/70
08/16/49
10/18/53
02/11/56
08/15/48
03/09/45
06/03/57
11/01/49
01/02/65
01/10/54
12/15/58
01/01/30
07/21/67
05/05/66
11/25/65
11/15/67
10/25/80
06/02/54
04/03/39
11/25/74
02/24/43
11/27/52
07/18/61
05/10/54
07/03/70
07/19/55
10/11/64
09/04/54
09/27/51
11/19/74
04/07/81
12/25/42
07/12/65
12/26/61
05/31/53
08/17/66
07/07/60
09/16/75
01/04/64
11/28/51
02/19/55
01/04/60
08/08/44
10/07/63
07/06/53
02/27/46
09/30/53
01/01/30
5/15/50
05/20/42
06/22/55
02/21/38
07/12/46
04/30/67
05/21/56
03/04/59
08/06/63
05/18/80
03/22/40
08/11/53
11/10/75
08/25/50
01/27/62
02/03/69
07/13/70
12/29/67
10/12/83
03/21/60
10/18/70
6/10/1971
05/27/82
02/22/54
01/06/53
04/23/33
06/09/60
05/19/87
06/25/57
10/22/58
12/05/54
05/27/61
01/30/41
03/19/52
12/11/54
06/02/56
09/27/47
11/23/71
12/02/58
05/05/84
03/14/54
05/07/46
12/03/39
10/08/73
02/27/65
08/25/42
11/17/55
10/10/40
08/05/57
08/19/63
02/16/51
03/06/78
11/10/58
03/04/55
01/31/59
11/20/57
01/01/30
09/28/53
01/01/30
11/27/74
03/25/67
06/18/62
04/30/59
07/11/62
05/06/36
06/06/66
01/22/60
07/04/40
05/27/79
07/24/70
04/05/70
04/28/67
10/24/74
06/05/40
08/15/71
07/05/41
08/02/48
11/10/73
11/22/81
02/11/81
11/29/75
01/01/30
09/18/65
06/07/75
01/08/43
11/08/47
05/21/45
04/21/54
07/28/47
01/08/48
10/21/53
05/19/52
01/12/48
08/10/54
01/03/33
09/09/53
08/12/54
01/28/33
September 3, 2009 at 12:04 pm
Ok, i played with the convert function and did something like
Select dateofbirth = Convert(datetime, DateOfbirth, 1) from studentcur
and i was able to get it in the format
1971-06-10 00:00:00.000
Why did one work but not the other? Obviously i don't understand that third parameter.
How do i get it back into the American format? Either MM/DD/YY or MM/DD/YYYY?
Thanks!
September 3, 2009 at 12:55 pm
Jacob,
1XX conversions require a four digit year, X conversions require a two digit year. I.E. 101 is the same format as 1, but 101 expects four digit years, 1 expects 2.
More information can be found here: http://msdn.microsoft.com/en-us/library/ms187928.aspx
September 3, 2009 at 1:39 pm
I saw that table. I didn't understand what it was saying. I've tried different formats and i get the same style each time.
I would like to format it better but I'm basically satisfied. I have a date instead of an error.
I don't see why this couldn't be simpler.
When i put in 10 i get teh same result: YYYY-MM-DD 00:00:00:000
Select dateofbirth = Convert(datetime, DateOfbirth, 10) from studentcur
Thanks!
September 3, 2009 at 1:42 pm
What style are you looking for? Just the date without the time?
There is no way to store just the date inside a datetime column, unfortunately
in 2008 there are a couple new data types, one of which is the 'date' type, which is the stripped out date from datetime.
September 3, 2009 at 2:31 pm
I'd like for it to be in MM/DD/YY or MM/DD/YYYY format. I really don't care about the time.
Geee! A whole day has gone by. I don't know if this was a shortcut after all. I hope i remember this the next time i have to do something like this.
September 3, 2009 at 6:23 pm
When you select a datetime value in SQL Server, it outputs it in ANSI format. This allows the interface to select the appropriate format. If you really must format it in T-SQL, then you'll need to use the Convert() function to convert it back to a string in the appropriate format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2009 at 9:11 pm
Heh... now I'm really going to tick you off... except when trying to convert from date formats that are in the basic form DD/MM/YYYY when the default date format is MDY, you don't need to explicitly define the conversion when converting to date time. For example, these all work correctly...
SELECT CAST('6/10/1971' AS DATETIME),
CAST('06/10/1971' AS DATETIME),
CAST('6-10-1971' AS DATETIME),
CAST('6.10.1971' AS DATETIME),
CAST('June 10 1971' AS DATETIME),
CAST('Jun 10 1971' AS DATETIME),
CAST('June 10, 1971' AS DATETIME),
CAST('Jun 10, 1971' AS DATETIME),
CAST('10 June 1971' AS DATETIME),
CAST('10 Jun 1971' AS DATETIME),
CAST('1971-06-10' AS DATETIME),
CAST('1971-6-10' AS DATETIME),
CAST('1971/06/10' AS DATETIME),
CAST('1971.06.10' AS DATETIME),
CAST('19710610' AS DATETIME),
CAST('6/10/71' AS DATETIME),
CAST('06/10/71' AS DATETIME),
CAST('6-10-71' AS DATETIME),
CAST('6.10.71' AS DATETIME),
CAST('June 10 71' AS DATETIME),
CAST('Jun 10 71' AS DATETIME),
CAST('June 10, 71' AS DATETIME),
CAST('Jun 10, 71' AS DATETIME),
CAST('10 June 71' AS DATETIME),
CAST('10 Jun 71' AS DATETIME),
--CAST('71-06-10' AS DATETIME), --2 Digit version doesn't work
--CAST('71-6-10' AS DATETIME), --2 Digit version doesn't work
--CAST('71/06/10' AS DATETIME), --2 Digit version doesn't work
--CAST('71.06.10' AS DATETIME), --2 Digit version doesn't work
CAST('710610' AS DATETIME)
There's simply no need to go through all the conversion headaches... let SQL Server do it for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2009 at 7:24 am
Thanks guys! I'm happy. When i looked into the SS2005 table it was in the appropriate format. thanks!
I still don't understand that chart but that is ok.
Best wishes.
September 5, 2009 at 2:26 pm
Jacob... this may help you understand the chart. Single digit styles are for character strings that show only the last two digits of year. Three digit character styles are for strings that show all four digits of the year. So use 3 if you are dd-mm-yy, and 103 if you are dd-mm-yyyy.
Jeff's examples are good for dates which are in some sort of month, day, year format, which is the default. SQL will also automatically handle year, month, day, if all four digits of the year are displayed.
select CAST ('03-06-10' AS DATETIME) = 2010-03-06 00:00:00.000
select CAST('2003-06-10' AS DATETIME) = 2003-06-10 00:00:00.000
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply