SQLSearcher
asked on
SQL Server convert day into date format
Hello Experts Exchange
I have a field in SQL Server 2012 that just has the day of the month in it, I need to convert the day into a full date.
For example the field would have 31 and I want it to become 31/07/2014.
Is there a SQL script that can do this?
Thank you for reading.
Regards
SQLSearcher
I have a field in SQL Server 2012 that just has the day of the month in it, I need to convert the day into a full date.
For example the field would have 31 and I want it to become 31/07/2014.
Is there a SQL script that can do this?
Thank you for reading.
Regards
SQLSearcher
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
the question to ask is if you have a value of 31, and are in the month of Feb, April etc where there is no such day: what should happen?
Hi,
My friend if the field contains 31 then there are total 8 months which has 31 Days so what should be the logic to pick which month to display for 31 .
Also have to set the same for 30, 28 , 29 (Feb in Leap year)
My friend if the field contains 31 then there are total 8 months which has 31 Days so what should be the logic to pick which month to display for 31 .
Also have to set the same for 30, 28 , 29 (Feb in Leap year)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>For example the field would have 31 and I want it to become 31/07/2014.
What's the logic for the 7 and 2014? If this is the current month/year, then SQL 2012 introduces EOMONTH, which returns the last day of the month for any given date.
What's the logic for the 7 and 2014? If this is the current month/year, then SQL 2012 introduces EOMONTH, which returns the last day of the month for any given date.
Turing only 31, without month and year, into a date will be an utter guess
Presumably there are values of 1 through 31, and turning any of those would be an utter guess too.
I would ask if you know of some inherent logic behind this curious field?
Is there anything else in a row to give us a clue on month and year?
(perhaps you could provide the column headings and a few rows of this data to look at?)
Presumably there are values of 1 through 31, and turning any of those would be an utter guess too.
I would ask if you know of some inherent logic behind this curious field?
Is there anything else in a row to give us a clue on month and year?
(perhaps you could provide the column headings and a few rows of this data to look at?)
--code to get "day_number" day of the current month
SELECT DATEADD(DAY, day_number - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
FROM (
SELECT 31 AS day_number UNION ALL
SELECT 17
) AS test_data
SELECT DATEADD(DAY, day_number - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
FROM (
SELECT 31 AS day_number UNION ALL
SELECT 17
) AS test_data
ASKER
Thank you for your help.