I am new to SQL world

Question

Post reply

I am new to SQL world

Viewing 10 posts - 16 through 25 (of 25 total)

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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

Kabina wrote:

Should I use declare variable above query or I can use in between as well ?

I though you said that you understood queries... 😉

As in most languages, you can declare variable pretty much anywhere before you need it. Variables will not general bridge to other scopes but can be made to do so using things such as sp_ExecuteSQL, which is normally used only when dynamic SQL is required to solve a given problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kabina Old Hand Points: 335 More actions · Answer 2

Thom A wrote:

This is pseudo-SQL, but seems like you'd be better off with a start and end parameter, which you set at the start of the batch:
DECLARE @StartDate date = DATEADD(DAY,-CASE DATENAME(WEEKDAY, GETDATE()) WHEN 'Monday' THEN 3 ELSE 1 END,GETDATE()),
        @EndDate date = GETDATE();

SELECT {Columns}
FROM dbo.YourTable
WHERE YourDate >= @StartDate
  AND YourDate < @EndDate;

Thanks Thom A my date format is 'yyyyddmm' greater than and less giving me error

DECLARE @StartDate date = DATEADD(DAY,-CASE DATENAME(WEEKDAY, GETDATE()) WHEN 'Monday' THEN 3 ELSE 1 END,GETDATE()),

@EndDate date = DATEADD(DAY, -1, GETDATE())

select * from Podata where POdate between CONVERT(CHAR(8), @Startdate, 112) and CONVERT(CHAR(8), @enddate, 112)

This reply was modified 5 years, 10 months ago by Kabina.
This reply was modified 5 years, 10 months ago by Kabina.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

Kabina wrote:

Thom A wrote:
This is pseudo-SQL, but seems like you'd be better off with a start and end parameter, which you set at the start of the batch:
DECLARE @StartDate date = DATEADD(DAY,-CASE DATENAME(WEEKDAY, GETDATE()) WHEN 'Monday' THEN 3 ELSE 1 END,GETDATE()),
        @EndDate date = GETDATE();

SELECT {Columns}
FROM dbo.YourTable
WHERE YourDate >= @StartDate
  AND YourDate < @EndDate;
Thanks Thom A my date format is 'yyyyddmm' greater than and less giving me error
DECLARE @StartDate date = DATEADD(DAY,-CASE DATENAME(WEEKDAY, GETDATE()) WHEN 'Monday' THEN 3 ELSE 1 END,GETDATE()),
@EndDate date = DATEADD(DAY, -1, GETDATE())
select * from Podata where POdate between CONVERT(CHAR(8), @Startdate, 112) and CONVERT(CHAR(8), @enddate, 112)

There will always be the rare exception (true with almost everything) but you need to stop using formatted dates. Don't store dates as character based data. Don't do comparisons based on character base dates.

What is the actual datatype of the POdate column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kabina Old Hand Points: 335 More actions · Answer 4

Jeff Moden wrote:

What is the actual datatype of the POdate column?

Datatype is nvarchar(48) but when I run Isdate() function it shows 1 which is valid date.

This reply was modified 5 years, 10 months ago by Kabina.

Thom A SSC Guru Points: 99143 More actions · Answer 5

Kabina wrote:

Jeff Moden wrote:
What is the actual datatype of the POdate column?
Datatype is nvarchar(48) but when I run Isdate() function it shows 1 which is valid date.

ISDATE() , like it's "friend" ISNUMERIC, should be avoided. Both can give false negatives, though ISNUMERIC is far worse at it.

TRY_CONVERT is a far better function to use. Why are you storing dates as an nvarchar though? Storing dates as a nvarchar will only lead to problems, and solve none that can't be solved elsewhere (such as in the presentation layer).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Kabina Old Hand Points: 335 More actions · Answer 6

Thom A wrote:

Kabina wrote:
Jeff Moden wrote:
What is the actual datatype of the POdate column?
Datatype is nvarchar(48) but when I run Isdate() function it shows 1 which is valid date.
ISDATE() , like it's "friend" ISNUMERIC, should be avoided. Both can give false negatives, though ISNUMERIC is far worse at it.
TRY_CONVERT is a far better function to use. Why are you storing dates as an nvarchar though? Storing dates as a nvarchar will only lead to problems, and solve none that can't be solved elsewhere (such as in the presentation layer).

This is backend tables are on SQL server where I got access to retrieve data where datatype for POdate is nvarchar(48).

Thom A SSC Guru Points: 99143 More actions · Answer 7

Kabina wrote:

This is backend tables are on SQL server where I got access to retrieve data where datatype for POdate is nvarchar(48).

That doesn't explain the data type choice. A varchar is a truly awful way to store dates; especially if you're not using one of the ISO formats ( for example yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnnnnnn). If, for example, you have values like N'01/02/03' then the value is utterly meaningless, as it could be one of several different dates:

01 January 2003
February 01 2003
2001 February 03
2001 March 02

Also, if you're storing dates in a style such as dd/MM/yyyy or MM/dd/yyyy then a "date" like N'01/07/2019' is before the a "date" like N'12/07/2000'.

There's also no reason to use an nvarchar to store a date. There are no unicode characters in a date. Certainly you don't need 48 characters to store one either. At most the date and time is going to be 26 characters, using the format yyy-MM-ddThh:mm:ss.nnnnnnn. Unless you're storing the name of the month, which opens an entirely more insane can of worms, and the order of your dates will likely be in alphabetical order of your months!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Kabina Old Hand Points: 335 More actions · Answer 8

Thom A wrote:

Kabina wrote:
This is backend tables are on SQL server where I got access to retrieve data where datatype for POdate is nvarchar(48).
That doesn't explain the data type choice. A varchar is a truly awful way to store dates; especially if you're not using one of the ISO formats ( for example yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnnnnnn). If, for example, you have values like N'01/02/03' then the value is utterly meaningless, as it could be one of several different dates:

<li style="list-style-type: none;">
<li style="list-style-type: none;">
01 January 2003

<li style="list-style-type: none;">
<li style="list-style-type: none;">
February 01 2003

<li style="list-style-type: none;">
<li style="list-style-type: none;">
2001 February 03

<li style="list-style-type: none;">
<li style="list-style-type: none;">
2001 March 02

Also, if you're storing dates in a style such as dd/MM/yyyy or MM/dd/yyyy then a "date" like N'01/07/2019' is before the a "date" like N'12/07/2000'.
There's also no reason to use an nvarchar to store a date. There are no unicode characters in a date. Certainly you don't need 48 characters to store one either. At most the date and time is going to be 26 characters, using the format yyy-MM-ddThh:mm:ss.nnnnnnn. Unless you're storing the name of the month, which opens an entirely more insane can of worms, and the order of your dates will likely be in alphabetical order of your months!

I dont understand if Podate is nvarchar as datatype when I run this query it gives good result. then why BETWEEN two dates are working fine if datatype is not date

SELECT PO, PODATE FROM PODATA WHERE PODATE BETWEEN '20191110' AND '20191115'

ORDER BY PODATE

This reply was modified 5 years, 9 months ago by Kabina.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 9

Just remember that the YYYY-MM-DD formats aren't actually followed as ISO in SQL Server even though it is a valid ISO format. For example, if you set the language = 'French', that particular format suddenly becomes YYYY-DD-MM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99143 More actions · Answer 10

Jeff Moden wrote:

Just remember that the YYYY-MM-DD formats aren't actually followed as ISO in SQL Server even though it is a valid ISO format. For example, if you set the language = 'French', that particular format suddenly becomes YYYY-DD-MM.

Only with the datetime data type. They fixed that "feature" with the new data types thankfully. I got fed up of having to remove - characters from dates. Oh course, a lot of people still use datetime, even if they don't use have a time.

Though now, in some ways, it bothers me more that the behaviour is dependant on the language and data type. ???????

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk