Trouble playing with dates

Question

Post reply

Trouble playing with dates

dany.fortier

Valued Member

Points: 60
More actions
April 30, 2015 at 7:23 am

#307534

i have trouble getting this to work properly:
SELECT MONTH(Anniversaire) AS MM, DAY(Anniversaire) AS DD, Anniversaire, Prenom, Nom FROM Tbl_RH_Pharmacie WHERE (Anniversaire <> N'') AND (MONTH(Anniversaire) = MONTH(GETDATE()))
ORDER BY MONTH(Anniversaire), DAY(Anniversaire)
the data type for Anniversaire is nchar.
The problem is that i want to exclude when the value in Anniversaire is empty or null. As soon as i execute the first part of the select --> SELECT MONTH(Anniversaire) AS MM, DAY(Anniversaire) AS DD, Anniversaire, Prenom, Nom FROM Tbl_RH_Pharmacie the query fail with Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
I can exclude some of the result in the where clause whan i do this : WHERE (Anniversaire is not NULL and Anniversaire != '') whan don't know how to correct it in the first part of the query.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

For the exclusion part, I would do something like

ISNULL(Anniversaire,'') <> ''

That should handle the empty string and the null problem.

The bigger problem is that the date is stored as a string. This combined with the out of range error likely means that the Birthdate / Anniversaire field has invalid values in it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

What format are the values in the anniversary field? It's a string so you'll need too know that to know how to parse it.

dany.fortier Valued Member Points: 60 More actions · Answer 3

YYYY-MM-DD is the format on the DB

I try this SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie but got the same error, Am i doing it correctly &

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

dany.fortier (4/30/2015)
YYYY-MM-DD is the format on the DB
I try this SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie but got the same error, Am i doing it correctly &

Try this:

set dateformat mdy;

SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie;

dany.fortier Valued Member Points: 60 More actions · Answer 5

Lynn Pettis (4/30/2015)
dany.fortier (4/30/2015)
YYYY-MM-DD is the format on the DB
I try this SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie but got the same error, Am i doing it correctly &
Try this:
set dateformat mdy;
SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie;

I have the same error The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 6

SELECT MONTH(Anniversaire) AS MM, DAY(Anniversaire) AS DD, Anniversaire, Prenom, Nom

FROM Tbl_RH_Pharmacie

WHERE (Anniversaire IS NOT NULL AND Anniversaire <> '') AND

1 = CASE WHEN ISDATE(Anniversaire) = 0 THEN 0

WHEN MONTH(Anniversaire) = MONTH(GETDATE()) THEN 1

ELSE 0 END

ORDER BY MONTH(Anniversaire), DAY(Anniversaire)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

dany.fortier (4/30/2015)
Lynn Pettis (4/30/2015)
dany.fortier (4/30/2015)
YYYY-MM-DD is the format on the DB
I try this SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie but got the same error, Am i doing it correctly &
Try this:
set dateformat mdy;
SELECT ISNULL(MONTH(Anniversaire),''),'', Anniversaire FROM Tbl_RH_Pharmacie;
I have the same error The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Then you have data that may look like this 2015-02-30 or some other invalid date. You'll have to validate the data in the column. this is why dates should be stored in columns defined using date or date/time data types.

dany.fortier Valued Member Points: 60 More actions · Answer 8

ScottPletcher (4/30/2015)
SELECT MONTH(Anniversaire) AS MM, DAY(Anniversaire) AS DD, Anniversaire, Prenom, Nom FROM Tbl_RH_Pharmacie WHERE (Anniversaire IS NOT NULL AND Anniversaire <> '') AND 1 = CASE WHEN ISDATE(Anniversaire) = 0 THEN 0 WHEN MONTH(Anniversaire) = MONTH(GETDATE()) THEN 1 ELSE 0 END ORDER BY MONTH(Anniversaire), DAY(Anniversaire)

Good this solution works !

Thank you.