January 25, 2010 at 3:26 am
I'm trying to insert fields from another database into my db, my problem is that the date field has been built with VARCHAR as the data type.
Dates are either 25.01.2010 or 25/01/2010
How is it possible to convert all the daes in format 25.01.2010 to a DATETIME?
January 25, 2010 at 4:28 am
Select convert(datetime, '25.01.2010', 104)
---------------------------------------------------------------------------------
January 25, 2010 at 9:26 am
Thanks for that, I've tried it and get the error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
This is the line I included
convert(datetime, S.[date completion sent to EAGA], 104),
January 25, 2010 at 10:06 am
Are you sure the dates are only in these two formats?
'25/01/2010'
'25.01.2010'
---------------------------------------------------------------------------------
January 25, 2010 at 3:47 pm
Thats all I have seen when I ran a query on just that field, but I'm going to go over it again to check
Checked again and made sure theres nothing else other than what i had mentioned.
This here is an example of what the field looks like
NULL
01/10/2009
01/11/2009
01/12/2009
02/11/2009
03/11/2009
03/12/2009
04.12.09
04/12/2009
05/11/2009
07/11/2009
07/12/2009
08/12/2009
09/12/2009
10/11/2009
11.11.2009
11/11/2009
12/01/2009
12/11/2009
14.10.09
14.10.2009
16.10.09
16/11/2009
19.10.2009
20.10.2009
20.11.09
20.11.2009
20/10/2009
20/11/2009
21/10/2009
21/11/2009
22.10.09
23.11.09
24/11/1998
24/11/2009
24/1109
25/11/2009
26.10.09
26.10.2009
26/11/2009
27.10.09
27.11.2009
28/10/2009
29.10.09
4.11.09
8.12.2009
January 26, 2010 at 2:35 am
Hi,
In your particular case I would just use this:
substring( field, 7, 4) + substring( field, 4, 2) + substring( field, 1, 2)
That's because a string in the format YYYYMMDD is always valid when converting to a date (you can update the date filed with this expression without conversion).
Best,
lx
January 26, 2010 at 2:54 am
Thanks for that, I tried it and managed to build it up like this below
Original field 26.10.09
Substring Field 091026
but what I'm looking for is how to convert the fields that are like 26.10.09 into dd/mm/yyyy
January 26, 2010 at 2:56 am
If your dates have different lengths I guess you'll have to split them and check if the year is 2 or 4 digits. I use this function to convert separate arguments do date.
best,
lx
CREATE FUNCTION [dbo].[DateSerial]
(
@year int,
@month int,
@day int
)
RETURNS Datetime
BEGIN
RETURN Right('0000' + LTrim(Str(@year)), 4) +
Right('00' + LTrim(Str(@month)), 2) +
Right('00' + LTrim(Str(@day)), 2)
END
January 26, 2010 at 3:02 am
If you use the udf you can change it to something like this:
CREATE FUNCTION [dbo].[DateSerial]
(
@year int,
@month int,
@day int
)
RETURNS Datetime
BEGIN
if @year < 30 --dates after 2000
set @year = 2000 + @year
if @year < 99 --dates between 1900 and 2000
set @year = 1900 + @year
RETURN Right('0000' + LTrim(Str(@year)), 4) +
Right('00' + LTrim(Str(@month)), 2) +
Right('00' + LTrim(Str(@day)), 2)
END
January 26, 2010 at 3:32 am
Not so elegant but this is what I could think of.. there could be an elegant way of doing it.
SELECT CONVERT(datetime, STUFF(REPLACE(<DateColumn>, '.', '/'), LEN(<DateColumn>)
- CHARINDEX('/', REVERSE(REPLACE(<DateColumn>, '.', '/'))) +2 , 4,
RIGHT(<DateColumn>, 2)), 3)
FROM <Your Tabe>
---------------------------------------------------------------------------------
January 27, 2010 at 5:34 am
Date should be in format MM-dd-yyyy to give it as varchar.
Eg.'01/25/2009'
try this one..
January 27, 2010 at 6:37 am
Hi There
I use a udf I found on the net a while ago and it has been working wonders for me with any form of date.
CREATE FUNCTION [dbo].[fnFormatDate] (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX ('YYYY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX ('Month',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('Mon',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX ('MM',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'MM',
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DD',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DD',
RIGHT('0'+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'D',
DATENAME(DD, @Datetime))
RETURN @StringDate
END
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD')
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD')
you choose your format
January 27, 2010 at 7:21 am
Excellent, thanks for this...
I will give it a go and hopefully all be ok 😀
Thanks again
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply