Date conversion - 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

Date conversion

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-06 : 00:01:36

Folks,

I've read a ton of the posts on creating functions for last day of the week. I have 2 major hurdles.

1. I can not use DECLARE @ or SET @ etc at the beginning of the query
due to the type of interface I'm using for query writing, it MUST
always start with SELECT
2. The datefield that I need to manipulate is not a DATETIME data
type field. When queried, the date
always shows up as YYYYMMDD. Both of the following statements
work fine when I just want to convert the date into a more read-
able format. I'm also able to use these in DATEPART functions:

SELECT (substring(cast(b.ldate as varchar(10)),5,2))+'/'+(substring(cast(b.ldate as varchar(10)),7,2))+'/'+(substring(cast(b.ldate as varchar(10)),1,4)) as 'Date by Convert',
(Substring(convert(varchar(10),b.ldate),5,2))+'/'+(Substring(convert(varchar(10),b.ldate),7,2))+'/'+(Substring(convert(varchar(10),b.ldate),1,4))
as 'Date by Cast'
FROM BOOKING B

The problem arises whenever I'm trying to do any DATEADD or DATEDIFF function. There is never a time associated with this field; time is handled in a different field for this application and it's not based off a timestamp....so it will only be in the 8 digit date like I mentioned.

Is there a way to use the datepart (WW) in conjunction with the above statements and then do some sort of case expression in order to take the week of the year and get the date? Or other suggestions.

Thanks!
Craig

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-06 : 00:07:05

One more note...if I do use DATEADD in conjunction with one of the above statements, it returns an error that says "SQL TYPE [11] NOT SUPPORTED" I'm assuming TYPE 11 is Datetime data type?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-06 : 00:20:49

Do you have table schema? It's possible to convert date and time to datetime type, and don't think type [11] is datetime since sql supports datetime.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-06 : 00:30:31

I'm not sure exactly what you need me to post. If you're asking what the data types are, I can look them up. Or what the primary keys are etc...when I run a query to select * from INFORMATION_SCHEMA.tables , it just says BASE TABLE.

Do you have some suggestions to try without seeing the schema?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-06 : 13:28:38

Table schema is all columns and their data type in a table.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-07 : 01:05:28

Rmiao,

K...That's what I kind of figured you meant. Did you just need the data type for the date field we have? Or all the date types of all fields?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-07 : 14:12:08

RMIAO,

I just looked up the 'LDATE' fields and it is SLONG PREC 10 WIDTH 10.

Thanks again!
Craig

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-07 : 15:17:30

It is really not clear from your post what you are after.

Also, I am wondering if you are really using Microsoft SQL Server?

If you are just looking for the last day of the week, this will do it:
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

CODO ERGO SUM

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-07 : 23:13:56

Hi Michael,

Thanks for your feedback. We are using MS Sql Server 2005.
What I want to do is be able to take the field called ldate which is a 10 digit
integer field and convert it to a date format so that I can use the dateadd function.

I have read your post that you referred to before. My problem arises that I can not use
the Declare statement at the beginning of any sql statement because the interface for our database that I have has limited rights; it only allows SELECT statements. I can't declare or set the cursor, do any update, insert or delete queries.

I was hoping to figure out a work-around so that I could add/subtract dates using a field that is not datetime.

Thanks,
Craig

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-08 : 15:18:20

Something like this might help??

DECLARE @Foo TABLE (LDate INT)

INSERT @Foo
SELECT 20080101
UNION ALL SELECT 20080513
UNION ALL SELECT 20080704
UNION ALL SELECT 20080707

SELECT DATEADD(MONTH,((LEFT(LDate, 4)-1900)*12)+LEFT(RIGHT(LDate,4), 2)-1,RIGHT(LDate, 2)-1)
FROM @Foo

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-08 : 15:44:17

quote:

Originally posted by Lamprey

Something like this might help??

DECLARE @Foo TABLE (LDate INT)

INSERT @Foo
SELECT 20080101
UNION ALL SELECT 20080513
UNION ALL SELECT 20080704
UNION ALL SELECT 20080707

SELECT DATEADD(MONTH,((LEFT(LDate, 4)-1900)*12)+LEFT(RIGHT(LDate,4), 2)-1,RIGHT(LDate, 2)-1)
FROM @Foo

This might be a little faster without the LEFT and RIGHT string functions.


SELECT dateadd(month,(12*(LDate/10000))-22801+((LDate/100)%100),(LDate%100)-1)
FROM @Foo

CODO ERGO SUM

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-08 : 21:36:21

Hey MVJ,

Thanks for the reply. I tried it and unfortunately it threw a string of errors:
==>ERROR at [2008/07/08, 19:32:35:378] cls:ATRowSet, obj::
Exception Thrown in SetRowBuffer (C:\auto\builds\11\Core\TpCo\ATRowSet.CPP line 1663):
Assertion failed: IsRow(nRow)

I was thinking about what you said on last post about SQL Server. I know we use SQL Server 2005, but I was doing some research into the data types we have available on the program/database we connect to. I see that slong is used in other SQL formats than SQL Server. Maybe it was written on a non-Sql Server format, but then was converted?

Thanks again for trying!
Craig

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-07-08 : 23:58:40

One more question MVJ, in the statement:
SELECT dateadd(month,(12*(LDate/10000))-22801+((LDate/100)%100),(LDate%100)-1)
FROM @Foo

The LDATE field is formatted to give date (but does not include time) in the format: yyyymmdd So when you are multiplying 12*(ldate/10000) is that supposed to be the month or the year portion? Without dateadd or the concatenation, it comes out to (12*(20080708/10000)-22801 OR 1295.8496. Just want to make sure that was the anticipated variable that you were expecting.

C

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources