Function for getting date

Question

Function for getting date

Hakuna Matata

SSCertifiable

Points: 5577
More actions
May 26, 2011 at 7:18 am

#250805

Hi,
I have an issue . The issue is that I need to retrieve data such that Travel Date should be greater than the current date. But my problem is that the data type of the travel date is nvarchar(max) and the year is not stored in the fields. The example date would be like 21May , 14Jun, 30Dec etc.
If the Travel date(04Jun) is greater than current date(26/05/2011) the date I have to retrieve should be like 04Jun2011. If the travel date(01Feb) is less than the current date the date retrieved should be 01Feb2012. How can I write a function to do this. The function has to take the string & retrieve me the date.
Regards,
Nithin

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

kr.nithin (5/26/2011)
Hi,
I have an issue . The issue is that I need to retrieve data such that Travel Date should be greater than the current date. But my problem is that the data type of the travel date is nvarchar(max) and the year is not stored in the fields. The example date would be like 21May , 14Jun, 30Dec etc.
If the Travel date(04Jun) is greater than current date(26/05/2011) the date I have to retrieve should be like 04Jun2011. If the travel date(01Feb) is less than the current date the date retrieved should be 01Feb2012. How can I write a function to do this. The function has to take the string & retrieve me the date.
Regards,
Nithin

ouch..so the string is stored as '21May', with no year? soy uo have to assume the current year?

first, I'd fix that in the application if you can, you'll spend more time fiddling with converting strings to dates than if you fix the app to use datetime fields...come December/January, when you have to revisit the rules all over again because of the year changeover, if you don't fix this up front you'll have trouble forever with it.

with luck, converting to datetime by adding the year will work, like in this example:

select

isdate('21May' + convert(varchar,year(getdate()))),

'21May' + convert(varchar,year(getdate())),

convert(datetime,'21May' + convert(varchar,year(getdate())))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 2

Thanks a looooooooooooooot!!!!!!!!!!!! the help provided.:-):-):-):-):-):-)

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 3

Thanks a lot for the valuable suggestion:-):-):-)!!!