Dateserial in SQL ? 1

[royalcheese]

Hi all

I am trying to convert a Access Query into SQL 2000 (using Query analyzer) .

In my old query i use dateserial(yearvar,monthvar,dayvar)

I have recreated the variables but now need some function to get them together as a date. Is there anytype of function i can use.

Much thanks in advance.

Chris

 

[royalcheese]

The way i have tried is

[/code]
CONVERT(DATETIME,'1'& '/' & MONTH(BPD.NRD) & '/' & YEAR(BPD.NRD) ,103)

Get an error on this ?

 

[bborissov]

The code should be:

[code]
CONVERT(DATETIME,'1/' + MONTH(BPD.NRD) + '/' + YEAR(BPD.NRD) ,103)

SQL Server string concatenation is not like VB/ACESS and you must use plus sign to concatenate strings.
BTW what DateSerial() function do in Access?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[royalcheese]

Using the code i get the error . . .

Server: Msg 245, Level 16, State 1, Line 9
Syntax error converting the varchar value '1/' to a column of data type int.


Dateserial (in JetSQL) , allows you make a date using three variables year , month and day

so to make the last day of the a month you say

Dateserial ( format(nrd,"yyyy"),format(nrd,"mm")+1,1) -1

month has a year added and set to the 1st and then you take 1 away to get the last day of the prevoius month

 

[royalcheese]

basically , I have the date stored in BPD.NRD

I want to find the last day of the month.

I used to do this in access using the code above using dateserial function.

Dateserial (format(nrd,"yyyy"),format(nrd,"mm")+1,1) -1

How can i do this in sql ?!

Many thanks

 

[royalcheese]

e.g

NRD holds 16/12/2017

I want to convert this into . . . .31/12/2017

 

[royalcheese]

ok found this stuff

dateadd(m, datediff(m, 0, dateadd(m, 1, bpd.nrd)), -1)


goodo thanks for helps

 

[bborissov]

Ooops, my mistake. You need to conver MONTH() also to varchar.
So the code must be:

CONVERT(DATETIME,'1/' + CAST(MONTH(BPD.NRD) as varchar(2)) + '/' + YEAR(BPD.NRD) ,103)

But if you want to get the last day of the month, try this:

-- To Get the last day of the month
SELECT DATEADD(dd,-DAY(BPD.NRD),DATEADD(mm,1,BPD.NRD))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP