Converting int into DATEs - 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

General SQL Server Forums

New to SQL Server Programming

Converting int into DATEs

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-04 : 17:22:28

I am trying to insert into a table this values:

Insert into #Holidays ( Holiday_Date, Holiday_Name)
values

('40560', 'Birthday of Martin Luther King, Jr.'),
('40924', 'Birthday of Martin Luther King, Jr.'),
('41295', 'Birthday of Martin Luther King, Jr.'),
('41659', 'Birthday of Martin Luther King, Jr.'),
('42023', 'Birthday of Martin Luther King, Jr.'),
('40536', 'Christmas Day'),
('40903', 'Christmas Day'),
('41268', 'Christmas Day'),
('41633', 'Christmas Day'),
('41998', 'Christmas Day'),
('40826', 'Columbus Day'),
('41190', 'Columbus Day'),
('41561', 'Columbus Day'),
('41925', 'Columbus Day'),
('42289', 'Columbus Day'),
('40728', 'Independence Day'),
('41094', 'Independence Day'),
('41459', 'Independence Day'),
('41824', 'Independence Day'),
('42188', 'Independence Day'),
('40791', 'Labor Day'),
('41155', 'Labor Day'),
('41519', 'Labor Day'),
('41883', 'Labor Day'),
('42254', 'Labor Day'),
('40693', 'Memorial Day'),
('41057', 'Memorial Day'),
('41421', 'Memorial Day'),
('41785', 'Memorial Day'),
('42149', 'Memorial Day'),
('40543', 'New Year’s Day'),
('40910', 'New Year’s Day'),
('41275', 'New Year’s Day'),
('41640', 'New Year’s Day'),
('42005', 'New Year’s Day'),
('40507', 'Thanksgiving Day'),
('40871', 'Thanksgiving Day'),
('41235', 'Thanksgiving Day'),
('41606', 'Thanksgiving Day'),
('41970', 'Thanksgiving Day'),
('40493', 'Veterans Day'),
('40858', 'Veterans Day'),
('41225', 'Veterans Day'),
('41589', 'Veterans Day'),
('41954', 'Veterans Day'),
('40595', 'Washington’s Birthday'),
('40959', 'Washington’s Birthday'),
('41323', 'Washington’s Birthday'),
('41687', 'Washington’s Birthday'),
('42051', 'Washington’s Birthday'),

However it gives me the error that Conversion failed when converting date and/or time from character string.

I assume i need to convert the integers into SmallDataTime which is the column type. What is the easiest way to do this? Thanks much for your help!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-04 : 17:32:08

This question makes no sense. You don't understand data types. You gave no process for doing a mapping. This sounds like the old joke about the teacher and the students:
teacher: "Mary, what is 6 times 7?"
student: "Red?"
teacher: "Billy, what is 6 times 7?"
student: "Thursday!"
teacher: "Johnny, what is 6 times 7?"
student: "42!"
teacher: "Johnny, tell the class how you got that answer."
student: "I divided Red by Thursday!"

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-04 : 17:52:12

Something like:

with holidays(datestring, holiday) as (
select '40560', 'Birthday of Martin Luther King, Jr.' union all 
select '40924', 'Birthday of Martin Luther King, Jr.' union all 
select '41295', 'Birthday of Martin Luther King, Jr.' union all 
select '41659', 'Birthday of Martin Luther King, Jr.' union all 
select '42023', 'Birthday of Martin Luther King, Jr.' union all 
select '40536', 'Christmas Day' union all 
select '40903', 'Christmas Day' union all 
select '41268', 'Christmas Day' union all 
select '41633', 'Christmas Day' union all 
select '41998', 'Christmas Day' union all 
select '40826', 'Columbus Day' union all 
select '41190', 'Columbus Day' union all 
select '41561', 'Columbus Day' union all 
select '41925', 'Columbus Day' union all 
select '42289', 'Columbus Day' union all 
select '40728', 'Independence Day' union all 
select '41094', 'Independence Day' union all 
select '41459', 'Independence Day' union all 
select '41824', 'Independence Day' union all 
select '42188', 'Independence Day' union all 
select '40791', 'Labor Day' union all 
select '41155', 'Labor Day' union all 
select '41519', 'Labor Day' union all 
select '41883', 'Labor Day' union all 
select '42254', 'Labor Day' union all 
select '40693', 'Memorial Day' union all 
select '41057', 'Memorial Day' union all 
select '41421', 'Memorial Day' union all 
select '41785', 'Memorial Day' union all 
select '42149', 'Memorial Day' union all 
select '40543', 'New Year’s Day' union all 
select '40910', 'New Year’s Day' union all 
select '41275', 'New Year’s Day' union all 
select '41640', 'New Year’s Day' union all 
select '42005', 'New Year’s Day' union all 
select '40507', 'Thanksgiving Day' union all 
select '40871', 'Thanksgiving Day' union all 
select '41235', 'Thanksgiving Day' union all 
select '41606', 'Thanksgiving Day' union all 
select '41970', 'Thanksgiving Day' union all 
select '40493', 'Veterans Day' union all 
select '40858', 'Veterans Day' union all 
select '41225', 'Veterans Day' union all 
select '41589', 'Veterans Day' union all 
select '41954', 'Veterans Day' union all 
select '40595', 'Washington’s Birthday' union all 
select '40959', 'Washington’s Birthday' union all 
select '41323', 'Washington’s Birthday' union all 
select '41687', 'Washington’s Birthday' union all 
select '42051', 'Washington’s Birthday')
Insert into #Holidays ( Holiday_Date, Holiday_Name)
select dateadd(day, cast(datestring as int), 0), holiday from holidays

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-05 : 01:35:39

Make sure the dates are correct. If this is originated from Excel, or Access, you need to add either 1 or 2 to the dates to be compatible with SQL Server.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources