SQL syntax to get MONTH, DAY from Excel columns

Question

SQL syntax to get MONTH, DAY from Excel columns

dwilcox

Old Hand

Points: 319
More actions
March 25, 2008 at 8:08 am

#124608

I have a simple Excel spreadsheet with 3 columns:
Name, DateOfBirth, DateToday
I've written a Data Flow that gets the Excel spreadsheet and writes a Birthday list to a flat file. However, I want the flat file written daily with only birthdays for each day, simple.
But I'm having trouble with the syntax in the SQL statement. I want to grab the 'MONTH' and 'DAY' from 'DateOfBirth' and compare it to the 'MONTH' and 'DAY' of 'DateToday'. This would give me the Birthdays for the current day. I've tried DATEPART and various others to grab the date from the spreadsheet but all error out.
What syntax can I use in an SQL statement to grab and compare the dates of this Excel spreadsheet, or is there a better way to compare the dates?
Thanks in advance for your help,
Dale

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQLWannabe SSCrazy Points: 2842 More actions · Answer 1

There are 2 ways I would go about this:

1) Import the spreadsheet into a SQL table (possibly using the Import/Export Wizard), THEN do your comparison. You also wouldn't need to store today's date in a field because you could use the getdate() function or

2) Have the users input the information via a webform into your new SQL table so you can do the comparison instead of using the spreadsheet as your data source.

Also, there may be some other way that you can retrieve the data from Excel, but I'm not familiar with them. HTH.

dwilcox Old Hand Points: 319 More actions · Answer 2

Thanks for your help, I hadn't thought of that, tried your idea and it works great. I'm still curious however what the syntax would be for SQL to extract MONTH and DAY from a date field in a XL spreadsheet. If anyone know maybe they'll post it.

SQLWannabe SSCrazy Points: 2842 More actions · Answer 3

I assume you know how to do it in T-SQL...... Month(DateField) and Year(DateField). You can use the same functions in Excel, Month and Year. Lookup functions in Excel and you should be able to get an example of each.