Cast Datetime and Null values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cast Datetime and Null values

Author

Topic

paulafernandes
Starting Member

10 Posts

Posted - 2008-01-31 : 11:39:37

Hello everyone!

I have on my SQL Server 2005 database a table with a two dates stored with the day, month and year on diferent fields as integers.
It has to be that way because the data is imported from a text file that as that format.

I need a query where it cast the dates as datetime, but I have a problem, sometimes one of the fields is empty and I get the error: "Conversion failed when converting datetime from character string".

This happens with the query that I built:


SET DATEFORMAT dmy;
SELECT DISTINCT 
                      Imagem, CAST(Dia1 + '-' + Mes1 + '-20' + Ano1 AS datetime) AS BeginDate, CAST(Dia1 + '-' + Mes1 + '-20' + Ano1 AS datetime) AS EndDate, 
                      ProcessData, Grupo + N'.' + SubGrupo + N'.' + Tarefa + N' -  ' + Dia1 + N'-' + Mes1 + N'-' + Ano1 AS Nome
FROM         Facts
WHERE     (CAST(Dia1 + '-' + Mes1 + '-20' + Ano1 AS datetime) >= ISNULL(@BeginDate, CAST(Dia1 + '-' + Mes1 + '-20' + Ano1 AS datetime))) AND 
                      (CAST(Dia1 + '-' + Mes1 + '-20' + Ano1 AS datetime) <= ISNULL(@EndDate, CAST(Dia1 + '-' + Mes1 + '-20' + Ano1 AS datetime))) AND 
                      (ProcessData = ISNULL(@ProcessData, ProcessData))

What can I do to avoid that error? I need to retrieve all records from the table with the complete dates or with blank dates.

Thank you!
Paula

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-31 : 11:45:07

Could you provide file layout (the date portions) and some sample data to work with?

paulafernandes
Starting Member

10 Posts

Posted - 2008-01-31 : 12:56:26

Hello jdaman, thank for answering!

I'm not sure if I understand what you're asking.
Is the text file?
Hope so, because it's this:

Day1 Month1 Year1 Day2 Month2 Year2
04 09 07 04 09 07
04 09 07 09 07
05 07 05 09 07

On the database the fields are all integers.
Like I said, I need to compose the dates, or return null when one of the fields are blank. For example, on the records above, it's missing the Day2 on the 2º record, and Month1 on 3º record.

I hope this helps to clarify...

Thank you!
Paula

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-31 : 13:27:45

[code]SELECT CASE WHEN DATEADD(yy, Year1 + 100, 0) < getdate()
THEN DATEADD(dd, Day1 - 1,
( DATEADD(mm, Month1 - 1,
DATEADD(yy, Year1 + 100, 0)) ))
ELSE DATEADD(dd, Day1 - 1,
( DATEADD(mm, Month1 - 1, DATEADD(yy, Year1, 0)) ))
END AS Date1,
CASE WHEN DATEADD(yy, Year2 + 100, 0) < getdate()
THEN DATEADD(dd, Day2 - 1,
( DATEADD(mm, Month2 - 1,
DATEADD(yy, Year2 + 100, 0)) ))
ELSE DATEADD(dd, Day2 - 1,
( DATEADD(mm, Month2 - 1, DATEADD(yy, Year2, 0)) ))
END AS Date2[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-31 : 13:52:27

[code]
select
MyDate =
dateadd(month,(12*(a.year+case when a.Year<50 then 2000 else 1900 end))-22801+a.Month,a.Day-1)
from
(
-- Test Data
Select [Year] = 7, [Month] = 8, [Day] = 31 union all
Select [Year] = 7, [Month] = 8, [Day] = 31 union all
Select [Year] = 7, [Month] = 8, [Day] = null union all
Select [Year] = 7, [Month] = null, [Day] = 31 union all
Select [Year] = null, [Month] = 8, [Day] = 31 union all
Select [Year] = 99, [Month] = 12, [Day] = 31
) a

Results:
MyDate
------------------------------------------------------
2007-08-31 00:00:00.000
2007-08-31 00:00:00.000
NULL
NULL
NULL
1999-12-31 00:00:00.000[/code]

CODO ERGO SUM

paulafernandes
Starting Member

10 Posts

Posted - 2008-01-31 : 14:01:35

Hello jdaman

I'm sorry, really sorry, but I made a mistake!
On the database the fields are not integers, but nvarchar...
Now it's more dificult to because I get the error "Argument data type nvarchar is invalid for argument 2 of dateadd function".

I'm trying to CAST it to integer, but it's not working...

Sorry for my mistake once again...

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-31 : 15:08:38

I stole from MVJ's solution for this working example:

DECLARE @a TABLE ( Day1 NVARCHAR(2), Month1 NVARCHAR(2), Year1 NVARCHAR(2), Day2 NVARCHAR(2), Month2 NVARCHAR(2), Year2 NVARCHAR(2) )
INSERT @a ( Day1, Month1, Year1, Day2, Month2, Year2 )
SELECT 04, 09, 07, 04, 09, 07 UNION
SELECT 04, 09, 07, NULL, 09, 07 UNION
SELECT 05, NULL, 07, 05, 09, 07 UNION
SELECT 05, 01, 98, NULL, NULL, NULL

SELECT
	MyDate1 =
	DATEADD(month, ( 12 * ( CAST(Year1 AS INT) + CASE WHEN CAST(Year1 AS INT) < 50 THEN 2000 ELSE 1900 END ) ) - 22801 + CAST(Month1 AS INT), CAST(Day1 AS INT) - 1),
	MyDate2 =
	DATEADD(month, ( 12 * ( CAST(Year2 AS INT) + CASE WHEN CAST(Year2 AS INT) < 50 THEN 2000 ELSE 1900 END ) ) - 22801 + CAST(Month2 AS INT), CAST(Day2 AS INT) - 1)
FROM @a

paulafernandes
Starting Member

10 Posts

Posted - 2008-02-01 : 07:15:13

Hello jdaman and Michael Valentine Jones!

I would like to thank you both, the solutions worked great!

Paula

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources