Tricky Date/Date Problem.

[tourcd]

Does anyone have any ideas how I might achieve the following in a SQL query?

I have an application that generates a date & time in a fixed format. I want to update a Date/Time field in SQL with the date generated by my application.

Unfortunately the date created is in the wrong format, for example todays date is passed as ‘2008630’. The time is also a problem, it's created in the format HHMMX, where X denotes AM/PM or 'T' for Military time. Delimiters aren't used either so there's no easy way of slicing up the text string.

Does anyone know how I can take a string in the format ‘YYYYMMDD HHMMX’ and transform in to something SQL is happy with before updating a record?

I think this would be relatively straight forward to figure out but the date can also be passed in the 'YYYYMDD', 'YYYYMD', 'YYYYDDM' formats making it more difficult and beyond my SQL knowledge

I'd be grateful for some pointers.

Thanks

 

[pjw001]

So you could receive a date as '2008111'.

Is that 1 November or 11 January.


I think that you need to rework the application.

 

[tourcd]

That's right, this is what makes it tricky! Unfortunately there's nothing I can do about the application

 

[JoeAtWork]

but the date can also be passed in the 'YYYYMDD', 'YYYYMD', 'YYYYDDM' formats making it more difficult and beyond my SQL knowledge

It doesn't matter how much SQL knowledge you have, if the data passed in is ambigious and formatted inconsistently then there is no solution.

Is there any way you can configure the application to use one consistent format all the time?

If not, it will be impossible to figure what the month and day are in cases like the one pjw001 pointed out. It's a problem that must be solved on the application side, not SQL server.

 

[SQLSister]

Well said Joe

"NOTHING is more important in a database than integrity." ESquared

 

[tourcd]

Fair enough. Thanks.

 

[tourcd]

Good news! I've got the application developers to do something about the date format described. Can anyone help with my original question?

"Does anyone know how I can take a string in the format 'YYYYMMDD HHMMX' and transform in to something SQL is happy with before updating a record?

 

[PWise]

try


select convert(datetime,'20080703 01:20p' + 'm')

 

[tourcd]

Thanks pwise but the time string I have to play with doesn't have a ':' inbetween hours and minutes, will you suggestion work in this case?

 

[SQLBill]

If it has an X, how do you know if the time is AM or PM?
What time would this be:

20080703 0100X

If you say the time could be 01:00 AM or 01:00 PM and you can't tell which one, then the time is still ambiguous.

If you can tell for sure, you could add 12 to the hour value and that would give 24-hour time format, then drop the X or T.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SajidAttar]

If we assume that the X will be the appropriate value for A or P.. you can get the datetime parsed through following syntax...

Declare @ADate varchar(20)
Select @ADate =  '20080111 0815a'
Select @ADate = Stuff(Stuff(Upper(@ADate),14, 0, ' ') + 'M', 12, 0, ':') 
Select Convert(datetime, @ADate)

HTH,


Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD